Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: USE_NL with or without ORDERED

Re: USE_NL with or without ORDERED

From: Shevtsov, Eduard <>
Date: Fri, 14 Sep 2001 02:18:43 -0700
Message-ID: <>

!! 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
> =
> 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 :-)


Please see the official ORACLE-L FAQ:
Author: Shevtsov, Eduard

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: (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).
Received on Fri Sep 14 2001 - 04:18:43 CDT

Original text of this message