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: Christian Trassens <>
Date: Thu, 13 Sep 2001 12:53:47 -0700
Message-ID: <>

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

The meaning of the word hint doesn't imply anything forceful. If you allow me, I may say that is a piece of advice. You say that CBO declines when hint is not possible, however as we saw at the end of his mail the path is feasible.

Stole from the Oracle forum (Doc ID: 143684.999) and from someone's email:


I am glad that the ordered clause worked for you. Here is another excerpt from Developement... "Optimizer hints are used to restrict the optimizer search space. For
example the ORDERED hint tells the optimizer only to consider one join
order (that specified in the FROM clause). Consider the query
select a.x from a, b where a.y = b.y;
in this case the optimizer considers all of the following alternatives:
a NL b
a HA b
a SM b
b NL a
b HA a
b SM a
But for the query
select /*+ ordered */ a.x from a, b where a.y = b.y; it will only consider the following alternatives: a NL b
a HA b
a SM b
it will not consider any of the alternatives which involve the join
order "b JOIN a", because the hint says look at the order of the
tables in the FROM clause and only consider alternatives which use
this join-order (i.e. "a JOIN b" in this case).

A join hint like USE_NL or USE_HASH also restricts the optimizer
search space. The USE_NL(yyy) hint will cause the optimizer to only
consider a nested-loops join when table yyy is the inner table of a
join. So if our query contained a USE_NL(a) hint select /*+ use_nl(a) */ a.x from a, b where a.y = b.y;

the optimizer would only consider the following alternatives:
a NL b
a HA b
a SM b
b NL a
The optimizer will not consider "b HA a" or "b SM a" because the hint
says that when "a" is the inner table, the optimizer should only
consider doing a nested-loops join."

If you would like your specific issue investigated further you can file an iTAR to provide all the necessary information to an RDBMS analyst. If development analysis is needed the analyst will be able to file a bug on your issue.

Oracle Support Services

In part I agree with you about statistics. However, as the note and I said the hint only restricts the paths. Therefore, it allows Oracle to change the path. So if you have accurated statistics, maybe it choose one path of the other.

Maybe it deals with the size of the hash area size or the setting of hash_multiblock_io_count.


Eng. Christian Trassens
Senior DBA
Systems Engineer
Phone : 541149816062

Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information
Please see the official ORACLE-L FAQ:
Author: Christian Trassens

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:53:47 CDT

Original text of this message