Re: How hints affect the CBO?

You've summed up very nicely the main issue - which is why I asked the question about the phrase 'semantically correct'.

The example as stated was:

>> select /*+ use_nl(t2) */
>> col_list
>> from
>> t1,t2
>> where
>> t2.id = t1.id

The path taken was

>> hash
>> table access full t1
>> table access full t2

The 10053 trace shows that Oracle has
obeyed the hint - i.e. when considering paths to join T2 to T1 Oracle has NOT considered a merge join or a hash join.

However, when considering paths from T2
to T1 (a join order that has not been forbidden) Oracle investigates the hash join (which has not been forbidden), and then does a 'swap join input' operation.

By the way - use_nl(t2) does not require an index on t2 for Oracle to do a nested loop. The pair of hints /*+ ordered use_nl(t2) */ will (should) force the nested loop to appear, even in the absence of an index on T2.

If you asked my opinion, I would say that the use_nl() hint as given is "semantically correct" but appears to be ignored. Even following your definition, viz:

>is the one that defines a valid access path and does
>not conflict with query and its environment

    A nested loop IS a valid access path     It does not CONFLICT with the query
    It does not CONFLICT with the environment

Since my interpretation of the hint is:

    "if you have a path which takes you INTO     table T2, then the only join mechanism that     you may use is a nested_loop join"
I can happily claim that the hint is semantically correct - but my statement of requirements was not complete.

However, despite any discussion we may have about Oracle's use of words - the important thing we agree on is that you have to be a little careful that your hints are correctly written, make sense, and circumscribe the optimizer's actions properly.

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

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Vladimir M. Zakharychev wrote in message ...


>Well, as far as I understood (I will not insist that my

>understanding is correct), 'semantically correct' hint

>is the one that defines a valid access path and does

>not conflict with query and its environment. In this

>case use_nl() does not seem to be correct as there

>is initially no index on t2(id). Adding an index does not

>seem to make it valid either, as t1 is not selected as

>driving table - CBO seemingly chose index on t2(id) to

>drive the join, which invalidated use_nl(). Adding

>ORDERED hint to use_nl makes the use_nl hint valid

>as now t1 is driving table and there is access path from

>t1 to t2 using index on t2.

>I verified this on 8.1.7.4 and ended up with hash join

>(full t2, full t1) if there is no index, hash join

>(ffs idx_t2_id, full t1) if there is an index on t2 and

>finally nested loops  (full t1, range scan idx_t2_id) as

>requested when ORDERED hint was added - as soon

>as NL access path opened, CBO stuck to it and didn't

>evaluate any other join methods.

>Not sure if my diagnosis is correct here, it would be very

>helpful if you take time to explain your example further.

>

>--

>Vladimir Zakharychev (bob_at_dpsp-yes.com)

http://www.dpsp-yes.com


>Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet

applications.


>All opinions are mine and do not necessarily go in line with those of my

employer.