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: Thu, 13 Sep 2001 12:42:10 -0700
Message-ID: <>

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

> Someone else commented on this as well a while back. This behavior is not
> unexpected and is documented and explained in the docs. But, your example
> doesn't exactly fit the scenario described in the manuals -- e.g. the CBO
> chooses as the "driving", or "outer", table the table for which the USE_NL
> hint was specified, thus rendering the hint "meaningless". Since your
> example contains no constraining criteria other than a join between the two
> tables, I am guessing that even though the table for which the USE_NL hint
> is specified is the inner table in the plan, the CBO is thinking it's going
> to return most of the rows from employee and goes ahead and does the FTS and
> HJ. This doesn't exactly fit the reason the docs give for using ORDERED with
> NL, but, it does seem like a logical approach, especially if one gets picky
> about the meaning of "forced" -- more on that below.
> Anyway, the 8i Tuning and Designing for Performance Guide includes the
> following comment in the section on join operations:
> "Use of the USE_NL and USE_MERGE hints is recommended with the ORDERED hint.
> Oracle uses these hints when the referenced table is forced to be the inner
> table of a join, and they are ignored if the referenced table is the outer
> table."
> Note the comment "recommended". Also note the comment "forced". In your
> case, employee isn't "forced" to be the inner table since the CBO "chose" it
> as the inner table. I suppose it would only be considered to be "forced" to
> be the inner table if the ORDERED hint was used. This is a slight change
> from the 7.3 docs which stated:
> "The USE_NL and USE_MERGE hints must be used with the ORDERED hint...."
> Notice the use of "must" instead of "recommended". Anyway, I guess you
> learned it is a good practice to include the ORDERED hint whenever using the
> USE_NL hint. FWIW, I have been aware of this behavior for quite some time,
> but, it wasn't until a couple of years ago that I stumbled across the
> comment in the docs regarding the ORDERED hint in conjunction with USE_NL.

Hi Larry,

thanks for your comments. I'm in doubt. Sorry for possible misunderstanding but the following is excerpt from the same (8i) doc: <blockquote>
The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the *inner* table. </blockquote>

Do you have any idea?


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 Thu Sep 13 2001 - 14:42:10 CDT

Original text of this message