Re: Where clause ordering

Matt Kott wrote:

> I have a question that I have gotten various answers on over time, so
> I'd like to ask it to all of you. When I write a query that contains
> more than one condition in the where clause, is it better to write the
>
> most delineating of them all first or last in the clause.
>
> For instance - a table has a month column ( Jan - Dec ) and a date
> column ( 1 - 31 ). Would it be better to say "...where month = 'May'
> and date = 21...", or "...where date = 21 and month = 'May' ?
>
> I've heard to put your most important one first, put your most
> important
> last, and that it didn't matter.
>
> Any help would be greatly appreciated.
>
> Matt Kott

   Firstly you should understand the concept how the optimizer works

1. If you have cost based optimizer and the table is analyzed , in that case it didn't matter
2. if you are using Rule base optimizer then only order matters
3. If your both columns are indexed then oracle evaluates in top down fashion, so most expensive should be last in WHERE clause
4. If both are not indexed then oracle evaluates in bottom up fashion so most expensive should be put first in where clause

hope its not confusing any more !

Brijesh Received on Sun Jun 29 1997 - 00:00:00 CDT