Re: USE_NL with or without ORDERED

!! 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?

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