Does the order in which we are giving the where conditions affect performance?

Question

I started learning oracle recently. I heard that there will be some performance issue(please correct me if i'm wrong) if we don't follow the order of where conditions {particularly when we are joining two or more tables} in an SQL Query. If that is the case what kind of order should follow and what are the factors i should consider. Please explain in general and for the below scenerio, Let's say I have 3 tables. Please find the DDL and Sample data here .

Table Part_material has basic information with ID as Primary Key(PK), Table Component will have different type of components with ID,part_name as PK, Every ID will have 30 to 40 Entries, Table connection will have connection information. I'm joining these three tables like below:

select com.id,part.part_material,com.part_name,'TRUE',part.map_id,part.quantity,'INBOUND'
                            from    Components com,Base_part part,connects rel
                            where   part.id=com.id 
                            and     dbms_lob.compare(com.part_value,'Coat wi........')=0
                            and     part.part_material in ('Barium','Tungston','Carbon')  
                            and     com.part_name='Build Material'
                            and     rel.fromid=part.id
                            and     rel.fromid=com.id
                            and     rel.relname in ('Export Need','Not Molten');

If I change the above order, will it improve/decrese the performance?

When the query is analyzed by the optimizer it will reorder your query, and perhaps even rewrite it if it feels it will make your query faster. So no, I don't expect that you will see any difference in performance by moving the conditions around (unless you change the semantic meaning of the query by doing so). But you can check this yourself by moving things around and comparing the explain plans. Just use an order that makes it readable, and why not use ANSI joins while you're at it. :) — sstan, Jul 15 '15 at 13:33
Pretty interesting question. I'm only guessing that MySQL does something similar to [short circuit evaluation](https://en.wikipedia.org/wiki/Short-circuit_evaluation), but then I found [this question](http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated). I would have guessed that the WHERE order would matter (comparing primary keys would be faster), but since different implementation of SQL iterate through the where clause in different directions, it's hard to say if changing the order will affect performance. — Dave Chen, Jul 15 '15 at 13:34
@sstan : Thank you. Ok i'll check the explain plan. And yeah i will start using ANSI joins :) — Arun Palanisamy, Jul 15 '15 at 13:43
@DaveChen . Thank you for the link :). That was new to me. I never knew about short circuit evaluation. — Arun Palanisamy, Jul 15 '15 at 13:43

score 3 · Answer 1 · answered Jul 15 '15 at 13:32

3

Oracle cost based optimizer is generally unaffected by the order of the WHERE clause.

Also answered by Tom Kyte back in 2009:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:1857060700346051220

answered Jul 15 '15 at 13:32

Ditto

3,256
1
14
28

If Tom Kyte says so, then I believe it. :) – sstan Jul 15 '15 at 13:37
Well, he's not often wrong, however, even he has said something along the lines of "If I don't prove it, challenge it." ;) – Ditto Jul 15 '15 at 13:43
no matter what Tom says, we cannot be 100%sure that rule-based stuff is hidden somewhere in the depths of Oracle. – PT_STAR Jul 15 '15 at 14:25

cristian v · Answer 2 · 2015-07-15T13:47:48.793

2

The execution strategy is decided by the optimizer and it isn't related to the order of the conditions in the where clause or the table order in the from clause. The syntax that you used is very old and error prone: start to use the JOIN syntax to write the conditions between the tables.

select com.id,part.part_material,com.part_name,'TRUE',part.map_id,part.quantity,'INBOUND'
                        from    Components com
                        join    Base_part part on part.id=com.id
                        join    connects rel on  rel.fromid=part.id
                                             and rel.fromid=com.id
                        where   dbms_lob.compare(com.part_value,'Coat wi........')=0
                        and     part.part_material in ('Barium','Tungston','Carbon')  
                        and     com.part_name='Build Material'
                        and     rel.relname in ('Export Need','Not Molten');

This is more error proof and clearer

edited Jul 15 '15 at 13:47

answered Jul 15 '15 at 13:38

cristian v

1,022
6
8

1

"*could sometime improve the performance as the optimizer can choose the better execution path*" - this is not true (at least for Oracle). The order of the join conditions and the type of the join (implicit join in the `where` clause vs. explicit `JOIN`) do not influence the performance. As a matter of fact, Oracle internal treats both join types absolutely identical. But I do agree that using an explicit `JOIN` is much better coding style. – Jul 15 '15 at 13:42
Completely agree with a_horse_with_no_name. I like your post, but that last part is not true. Consider editing it, or finding and including information that backs up your claim. – sstan Jul 15 '15 at 13:46
*"The syntax that you used is very old and error prone"*... um, actually, the Oracle Optimizer can and does rewrite the ANSI syntax into the old-style join behind the scenes. Check out the explain plan for `select * from dual d1 left outer join dual d2 on (d1.dummy = d2.dummy);` - under the predicate section, I see `3 - filter("D1"."DUMMY"="D2"."DUMMY"(+))`. That's on 11.2.0.2; I'm not sure if it's still the case in v12. It's actually more likely that the ANSI style is more error prone (and in fact, there were lots of bugs with it when it was first introduced!) – Boneist Jul 15 '15 at 13:59
(Although don't take that as me being against the ANSI syntax join; it's taken a while, but I'm warming up to it! *{:-) ) – Boneist Jul 15 '15 at 14:01
@Boneist: By "error prone", I think he means that as the person writing the SQL, I can more easily write an incorrect query using the old join syntax vs. the ansi join syntax. This is especially true with left joins. All it takes is to forget one little `(+)` in one single condition, and your query is broken. I used to be a hardcore old join syntax guy. But I'll be the first to admit that my queries have less bugs since I've switched to the ansi join syntax. – sstan Jul 15 '15 at 14:15
Ah, yeah. Good point! Although I struggle with ANSI joins when the join condition isn't straight forward, especially with outer joins; deciding whether to place a predicate in the join or where clause isnt' always straightforward IMO! Six of one, half a dozen of the other, I guess! – Boneist Jul 15 '15 at 14:18

score -1 · Answer 3 · answered Jul 15 '15 at 14:23

-1

if you use the rule-based optimizer (or the +rule hint) the order of the where-clause matters a lot. But it is strongly recommended to use the cost-based optimizer. Be aware: as long as Oracle supports the rule-based optimizer you can not be 100% sure that the rule-based stuff is hidden somewhere in the code ... no matter what Tom Kyte (the SQL-Guru) says.

answered Jul 15 '15 at 14:23

PT_STAR

505
1
4
13

why downvoting? On the german Oracle Conference (DOAG) a speaker was telling us, that if all else failes, he somtimes uses the +rule hint! And this was a guy of the most renowned and famous Oracle consulting companies in central Europe. And btw this opinion is independend of Oracle! – PT_STAR Jul 21 '15 at 12:53

Does the order in which we are giving the where conditions affect performance?

3 Answers3