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@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.
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:1025522441.881.0.nnrp-14.9e984b29_at_news.demon.co.uk...
>
> 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.
> >
> >
> >
> >
>
>
Received on Mon Jul 01 2002 - 11:33:48 CDT
