Re: USE_NL with or without ORDERED

Hi Larry, Jonathan

thanks for your answers. I haven't heard about "sides swapped" effect before your postings. Larry, I
had a thought about exact the same path (I mean the links you posted :-) to go into details.
Jonathan has already posted the info I was going to check That's enough for my current understanding.

Thanks again,
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).
>

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