SQL code tuning

A very good a useful information to all ,Sybrand. Thanks. The Oracle Documentation does not mention this information you have given. It also does not mention that the effects of ordering of columns.

But, maybe i am reading the wrong documentation . I have 8i in which CBO has become popular.
My experience wiht Rule based optimizer is - its best to be avoided. I have seen **dramatic*** differences in "Explain Plan" output by just Rearranging the columns of the "From" Clause. Its a mess to work with the RBO, because its not based on any sound logic. Nowadays the Cost Based Optimizer is pretty smart to figure out the Driving and driven tables in a join, indexes to be used etc. Best to use it.

Regards
Mark

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<tsj4tlj3qt4960_at_corp.supernews.com>...
> "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 Mon Oct 15 2001 - 04:03:53 CDT