Re: USE_NL with or without ORDERED

!! Please do not post Off Topic to this List !!

> I think this is the best description I have seen
> so far of the way in which the optimiser has
> evolved.
>
> Currently I believe that the optimizer HAS to
> obey hints if the hints can be applied to the
> paths it has been constrained to check. If this
> were not so, the new 'plan stability' feature
> of 8.1 could not work as it relies on storing
> hints in the database and applying them at
> run time.
>
> However, in the latest versions of Oracle
> you need to ensure that Oracle is not
> allowed to check any paths that do not
> match your exact requirements - and
> use_nl all by itself is not strict enough.
> Take a look at how many hints Oracle
> pushes into user_outline_hints the next
> time you try to produce a really simple
> plan.
>
> For your entertainment - here's an example
> even more surprising than the one you produced:
>
> select
> /*+ use_nl(t2) */
> t1.cols, t2.cols
> from
> table1 t1, table2 t2
> where
> t1.id = t2.id
> and t1.restriciton = {literal};
>
> desired plan:
> nested loop
> full scan of t1
> indexed access into t2
>
> Actual path:
> hash
> full scan of table 1
> full scan of table 2
>
>
> How did Oracle manage to come up
> with a plan that visited the tables in the
> right order, and STILL ignore the use_nl
> hint for getting into the second table.
>
> Left as an exercise to the interested reader -
> but I will post the answer in a couple of days
> if anyone wants it.
>

Hi Jonthan,

thanks for the explanation. I'm interesting in seeing your answer. It seems Oracle should rename USE_NL into USE_NL_or_HASH :-)

Regards,
Ed

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Shevtsov, Eduard
  INET: EShevtsov_at_flagship.ru

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).