Re: SQL code tuning

"Jason" <foucault4_at_home.com> wrote in message news:uzfy7.128431$5A3.40183995_at_news1.rdc2.pa.home.com...
> hi everyone...
> i've been combing all the books and online docs i can find looking for
> information on the order in which oracle interprets an sql statement and
how
> changing the code may affect performance. i know oracle reads the clauses
> (From clause, Select clause, etc.) in a particular order, but what i can't
> seem to find is does it matter what order you put the joins and/or
selection
> criteria in the WHERE clause? For example, is there a difference between
> the following where clauses in terms of how oracle executes them?
>
> select ...
> from t1
> where t1.f1 = 'abc'
> and t1.f2 = 'xyz'
> and t1.f3 = 123
>
> where t1.f3 = 123
> and t1.f2 = 'xyz'
> and t1.f1 = 'abc'
>
> i've heard that oracle will evaluate the criteria in reverse order, that
is
> from the bottom up so that the last criteria in the clause is the first
one
> to be searched for in the table or index. however my experience seems to
> indicate this is not true. does anyone know for sure or could you point
me
> to a doc that discusses this?
> thanks!
>
>
>

The order only is important when the optimizer doesn't manage to find an optimal path. So when all things are equal the *rule based optimizer*, which is obsolete since 1994, and which you shouldn't use anymore, will use the rightmost table, as this optimizer parses from right to left. The *cost based* optimizer will use the tables from left to right. You can force CBO to do this by using the /*+ ordered */ As to the where clause:
in the rule-based optimizer you should place the most restrictive clause *last*, using CBO it doesn't matter.
You should however use parenthesis liberally in order to avoid the optimizer to assign incorrect priorities.
example:
numcol = :b1 and datecol >= :b2 and datecol <= :b3 this will be treated as
(numcol = :b1 and datecol >= :b2) and datecol <= :b3 and the index will not be used
numcol = :b1 and datecol between :b2 and :b3 and the index will be used

I think any decent tuning book, and most likely also the Oracle will have info on the order issue

Regards,

Sybrand Bakker
Senior Oracle DBA Received on Sun Oct 14 2001 - 08:23:52 CDT