Order of the where clauses

[kmfna]

Hello all,

I was curious, do the where statements in queries work from the top down or bottom up? ie. would you want to put the statements that narrow the return down by the most first or last?

Thanks,
Kevin

- "The truth hurts, maybe not as much as jumping on a bicycle with no seat, but it hurts.

 

[sem]

Unless the opposite is specified in hint, in 9i the order of conditions doesn't matter at all. In earlier versions it was necessary to keep the order of conditions the same as the order of columns in index if you planned to use it. To get better understanding you may read about how Oracle optimizer works here:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm

Regards, Dima

http://seminihin.narod.ru

 

[kmfna]

cool..thanks!

Kevin

- "The truth hurts, maybe not as much as jumping on a bicycle with no seat, but it hurts.

 

[dbtoo2001]

You may want to review Oracle Metalink Note:276877.1.
Subject: How the performance of the query is influenced by its predicate order.

According to the article:

Some of the things that influence the order of application of the predicates are:
- Their position in the WHERE clause.
- The kind of operators involved
- Automatic Datatype conversion.
- The presence of bind variables
- Joins
- The optimizer mode (Rule or Cost)
- The cost model (IO or CPU)
- The Query Rewrite feature
- Distributed operations
- View merging
- Query Un-nesting
- Other Documented and Undocumented Parameters
- Hints

From the Document that SEM references:
If you do not use the ORDERED_PREDICATES hint, then Oracle evaluates all predicates in the following order:

Predicates without user-defined functions, type methods, or subqueries are evaluated first, in the order specified in the WHERE clause.

Predicates with user-defined functions and type methods that have user-computed costs are evaluated next, in increasing order of their cost.

Predicates with user-defined functions and type methods without user-computed costs are evaluated next, in the order specified in the WHERE clause.

Predicates not specified in the WHERE clause (for example, predicates transitively generated by the optimizer) are evaluated next.

Predicates with subqueries are evaluated last, in the order specified in the WHERE clause.

 

[Turkbear]

Hi,
That same Metalink note :

One of the things that the optimizer do when a query is parsed, is to looks into the predicates listed in the WHERE clause and try to establish the order in which to apply them to filter the rows.

( Great spell/grammar checking in Metalink, eh)

The optimizer will reorder the predicates if the table has been analyzed and Cost stats are available...The user-supplied order will be rewritten when appropriate.





To Paraphrase:"The Help you get is proportional to the Help you give.."