Re: Is Richard Niemiec Right

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 20 Sep 2003 22:03:57 +0100
Message-ID: <bkif9q$3t6$1$830fa7b3@news.demon.co.uk>

Comments in-line:

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____USA__October
____UK___November

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "joe bayer" <joebayerii(no-spam)@hotmail.com> Newsgroups: comp.databases.oracle.server Sent: 20 September 2003 18:33 Subject: Is Richard Niemiec Right

> In Richard Niemiec's book "Oracle 9i Performance Tuning, Tips &
> Techniques", Chapter 9, advanced SQL tuning, he mentioned about
selecting
> the driving table for table joins, he mentioned
> 1) we should put most selective condtion in the where clause to
eliminate
> the most unqualified rows

It looks as if you have missed a word or two out of (1) - after all you have to put all the necessary conditions in the WHERE clause or you don't get the answer you want. I'll guess the missing word is FIRST - in which case there is a special case where the statement is true, but only if you are not using Oracle 9 properly.

> 2) in the three table join situation, we should put the table which
join
> with the other two tables as the driving table, and put the driving
table as
> first table after the from clause.
>

As a general guideline, this is obviously complete rubbish on two counts. Consider:

    select

        a.col1,
        b.col2
    from
        join_table        j,
        keyA_table    a,
        keyB_table    b
    where
            a.highly_selective_indexed_col  = 'const'

    and j.a_key_indexed = a.key_col
    and b.key_col = j.b_key
     ;

Clearly the smart join order is likely to be:

    A, J, B
Fortunately, the cost based optimiser would tend to spot this regardless of the order of the tables in the FROM clause

> Is this statement true?
>
> By testing, I found that changing the table position in the from
clause or
> changing the position of join conditions at where clause does not
affect
> optimizer at all, if you do not put ORDERED, or RULE hint.
>
> My understanding is, optimizer will calculate all the possibility
of all
> possible join conditions up to 80,000 or something like that, so it
should
> not matter where you put your table, or which join conditions first,
unless
> it has more than 10 table joins.
>

You seem to be better informed than Richard Niemiec - but it is possible that the book contained some qualifying conditions to explain the circumstances under which his claims appeared to be true.

In fact, Oracle will rarely examine an extreme number of permutations - it tends to short-circuit the search in almost all cases, and has various algorithms that (usually) lets it get to a good plan after examining far fewer. The 80,000 is a figure from Oracle 8 and earlier by the way, from the parameter called optimizer_max_permutations. Oracle 9 has a default of 2,000 for this parameter, which modifies the permutation strategy.

The limit of 10 doesn't look familiar - there was a critical limit of 5 expressed in another parameter _optimizer_search_limit, which would also have an impact on the permutation order, and this may be the one you are thinking of.

> You imput is highly appreciated.
>
>
Received on Sat Sep 20 2003 - 16:03:57 CDT