Re: How hints affect the CBO?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1025545736.12760.0.nnrp-14.9e984b29_at_news.demon.co.uk...
>
> 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.
>

Thanks for the explanation.

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

Well, without an index use_nl() doesn't make sense at all, so, even though it would be semantically correct, it would also be plain dumb thing to do as it results in an FTS of T2 for each row from T1. :)

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

Aha. I knew there's a catch. :)

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