RE: USE_NL with or without ORDERED

!! 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
elkinsl_at_flash.net
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: elkinsl_at_flash.net

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