2

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.

Robert Bain
  • 9,113
  • 8
  • 44
  • 63
  • 2
    It would be interesting to see the explain plans; can you please post them? Also, which Oracle version? – Aleksej Jan 26 '17 at 15:07
  • Yeah, this is pretty interesting. For SQL Server it is purely semantic. –  Jan 26 '17 at 15:11
  • @Aleksej I'm afraid I can't, I'd be in violation of the security policy at work. Thanks for your help though! – Robert Bain Jan 26 '17 at 21:42
  • Are there any profiles, outlines, or SQL Plan Baselines in the "Notes" section of the explain plan? Changing the text of the query may prevent those things from working. – Jon Heller Jan 26 '17 at 23:48

1 Answers1

1

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

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • I think this is what's happening. Adding the check to the where clause changes the join order in execution plan. I'm going to experiment with the ORDERED hint and see if I can get the same behaviour without the repeated condition. – Robert Bain Jan 26 '17 at 15:34
  • @RobertBain . try and let me know ... if you mean the sequence in which the join are writtens this remember me the way the query optimizer was influenced when there is not explicit join sintax and the all the tables was written side by side comma separated .. then the most right was name the lead table because was evaluetedc first in table scan .. could be also the with explicit join a new query optimizer this concept is lost and then the where addiction force this behavior .. hope di comment is useful .. – ScaisEdge Jan 26 '17 at 15:38
  • scaisEdge, see the update above. I don't have a conclusive answer. – Robert Bain Jan 26 '17 at 23:41
  • I think is like i suggested at the end of my previous comment .. the sequence of join don't allow at the query optimizer to find the lead table ( the better starting scan for query) .. and the on condtion in join don't add info at the query optimizer ... instead addig a where condition introduce an new element for query optimizer evaluation .. that force the scan staring qwith this .. – ScaisEdge Jan 27 '17 at 07:13