CODEWITHSUNDEEP
sqloracle-databasesql-tuning
Robert Bain
Robert Bain

Reputation: 9586

Adding a join condition in the from clause *and* where clause makes query faster. Why?

I'm tuning a query for a large transactional financial system. I've noticed that including a join condition in the where clause as well as the from clause makes the query run significantly faster than either of the two individually. I note that the join in the from clause has more than one condition; I mention this in case it is significant. Here's a simplified example: 

SELECT  *
FROM       employee e
INNER JOIN car c ON c.id = e.car_id AND -- some other join
-- Adding the join above again, in the where clause makes the query faster
WHERE c.id = e.car_id;

I thought ANSI vs old-school was purely syntactic. What's going on?

Update

Having analysed the two execution plans, it's clear that adding the same join in the where clause as the from clause, produces a very different execution plan than having the join in either of the two.

Comparing the plans, I could see what the plan with the additional where clause condition was doing better, and wondered why the one without, was joining in the way that it was. Knowing the optimal plan, a quick tweak to the join conditions resolved matters, although I'm still surprised that both queries didn't compile into the same thing. Black magic.

Upvotes: 2

Views: 266

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

could be that the WHERE c.id = e.car_id addition is a way for control the order in which the tables are used to perform the proper search .. this could a way for forcing the query optimizer to use as main table the table in where condition and the table related beacause the the sequence of table joins could not so valid for searching as is usefull for understand the query logic

Upvotes: 1

Related Questions