Re: Order in Where Clause

In article <ronoCtD8HH.LAM_at_netcom.com> rono_at_netcom.com (Ron A. Olshavsky) writes:
>Can anyone give a decent answer to how thigs should be ordered in a WHERE
>clause of a lrage multi-table select?
>
>Ive heard it matters a great deal, to it doesnt matter at all...
>
>I know that the FROM is very important, and simply chaing the order the
>tables are listed in cut a large query time in half...
>
>Can optimizing the WHERE statement help anymore?

It has been my experience that the order of the tables and the elements within a WHERE clause have no effect under normal circumstances. Normal means that the elements of the WHERE clause determine how Oracle will process the tables. If the table involved are indexed and Oracle can use the indexes to search for rows, Oracle will use it rules of precedence.

Single column unique indexes are used first. Multi-column indexes with a like test are used last. The indexes are vastly more important than the order of the tables and where clause elements. At least that's what I get from reading Oracle documentation, reviewing Explain Plan output, and my own experience.

-- 
============================================================================
Michael Sallwasser  | Down one path is utter dispair and hopelessness. Down 
Northrop Grumman    | the other is total destruction. Let us choose wisely.