Re: How hints affect the CBO?

It's not a crime to contradict an Oracle note, of course ;) However, I think in this case I was enhancing it.

If you write:

    select /*+ use_nl(t2) */

            col_list
    from

            t1,t2
    where

            t2.id = t1.id
;

Is the hint syntactically and semantically correct ? Does the answer to the question change dependent upon the existence of
an index on t2(id) ?

If Oracle produces the plan:

        hash
            table access full t1
            table access full t2

is it a bug ?

In this case, it is easy to prove that Oracle has (a) obeyed the hint and (b) found a
path that appears to ignore the hint.

If you don't mind, I will postpone explaining why this happens until we have decided the interpretation of 'semantically correct'.

--
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 ...


>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>

>wrote in message

news:1025341279.8484.1.nnrp-14.9e984b29_at_news.demon.co.uk...


>

>> Hints are mandatory if valid AND if there is

>> no way of avoiding the hint.

>

>Hmm... Excuse my ignorance, but doesn't this contradict

>with the quote from 69992.1? It defines a valid hint as

>both syntactically and semantically correct and states that

>if it is ignored in this case, then this is a bug. You say

>there still may be a case when Oracle will avoid this 'locked'

>access path and this won't be a bug? And why Oracle

>would look for a way to avoid the hint? Can you please

>clarify?

>

>--

>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.


>

>

>

>