Re: Where clause ordering

Jonathan Lewis (Jonathan_at_jlcomp.demon.co.uk) wrote:
> Here's a quirky little example of optimiser implementation I came
> across today.
> As you probably know, all other things being equal the predicates
> in a WHERE clause for a table are evaluated from bottom to top,
> so:
 CORRECT
> select count(*) from lookups
> where
> class = 123
> and to_number(value) = 0;
> could in theory run faster than:
> select count(*) from lookups
> where
> to_number(value) = 0;
> and class = 123
 NO.
 Because you have a function on the value field.  Regardless of the fact that class is before or after the value field,  Oracle would always use class index.

 I understand that the author probably knows that and this just slipped  his attention; but generally one should try to not rely on the ordering  of the where clause. It is better to explicitly suppress the indexes  that you don't want used rather than relying on Oracle's parsing  technique.

 FROM clause is also processed from right to left. There it is impossible  to not rely on the parsing technique since that is the only way to  specify the driving table if all other things are the same between two  tables.
 Even there one should run EXPLAIN to make sure, because regardless of  where in the FROM clause, you have a table; Oracle would use the  table where UNIQUE INDEX COLS = CONSTANT as the driving table.   

 In short, always run explain for even slightly complicated queries  to make sure Oracle would run it as you think Oracle should run it.

• Saad Ahmad *
• McHugh Freeman & Associates, Inc. *
• Ph: (414) 798-8606 Ext. 457 *
• Fax: (414) 798-8619 *
• E-Mail: sahmad_at_mfa.com *

  ---