!! Please do not post Off Topic to this List !!
Ed,
Do I have any idea? Yeah! The docs can be very confusing at times ;-).
Maybe they are just clarifying the specified table is the inner table as
opposed to the outer table of an NL join (which would be rather obvious to
you or me, but, maybe not to someone else "new" to Oracle, CBO, and hints)?
But I agree, reading the comment seems to imply the use of USE_NL forces the
target to be the inner table. I guess they are assuming one has read the
preceding comment about the ORDERED hint in conjunction with
USE_NL/USE_MERGE? Still, pretty confusing and could be worded a bit better.
And forget that other stuff I wrote about why your original query, even
though driving in the correct order, decided to use an HJ. I usually
investigate things instead of making off the cuff remarks. I didn't in that
case -- shame on me. Anyway, Jonathan Lewis's posting prompted me to pursue
it further.
I *think* what you are seeing is a "sides swapped" on an HJ. Because you
didn't specify an ORDERED hint, it also evaluated join methods for a join
order of employees to courses. During the HJ calculation for this order, a
"sides swap" (meaning swapping the inner/outer inputs?) was done and was the
least costly. Do a 10053 trace on your statement where it has just the
USE_NL hint and does an HJ in the correct order of how you want to do the
NL. Take a look in the second set of join calculations in the trace file and
see if you see "sides swapped" in the HJ (HA Join) calculation section.
There isn't a lot of information out there on the 10053 traces. I have seen
some info on the Steve Adams site (www.ixora.com.au), and, there is a paper
at http://www.evdbt.com/library.htm (it's down towards the bottom). There
are two places in the comments of that paper where hash joins and swapping
the inputs is mentioned -- the second place it is referenced is what has me
going down this path. Plus, I think Jonathan Lewis once mentioned on this
list the possibility of inputs getting swapped. Searching on-line, various
white papers, etc, I haven't come up with a hit on the "sides swapped"
mentioned in the 10053 trace (I have come across info about possible inputs
switching once the hash join is underway). But, based on that 10053 paper,
and the use of the term "sides swapped" in the trace file, it sounds like a
strong possibility that this is what is happening -- it also evaluates the
joins for an employees, courses order, flipping the inputs on the HJ, sees
it is the lowest cost, and chooses it. Bingo, the order you wanted but still
using an HJ.
Of course, this is all conjecture.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
> -----Original Message-----
> Sent: Thursday, September 13, 2001 3:51 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: USE_NL with or without ORDERED
> 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: Larry Elkins
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
