ALL_ROWS would indeed prefer hash join and S/M over NL. On 2004.01.06 17:14, Jay Wade wrote: > Thanks again for all your help. > Does the ALL_ROWS hint force a preference in Join Types as well? > If so would it try to force a HASH JOIN? > > > >From: Mladen Gogala <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: Re: Re[2]: OCP Question (Perf Tuning) > >Date: Tue, 06 Jan 2004 13:24:25 -0800 > > > >Oracle doesn't but Jonathan Lewis does, in his "Tutorials". I found out > >about that > >from Scott Gosset in 8i internals class in NYC. Seems still to be true. > >Gospel of Jonathan should suffice, however. I just discovered that my > >10053 trace name errorstack forever, level 12 causes ORA-600 in 9.2. Let me > >look > >for a patch and I'll come back later. As for the question being nontrivial > >and unfair, > >I agree. > > > > > >On 2004.01.06 14:29, Jonathan Gennick wrote: > > > Tuesday, January 6, 2004, 1:59:26 PM, Mladen Gogala > >([EMAIL PROTECTED]) wrote: > > > MG> Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL > >to all other > > > MG> methos despite the price. > > > > > > Does Oracle themselves *document* that what you say is the > > > case? I believe you, but I'm not sure that Oracle documents > > > what you just said, so short of looking at the code, I'm not > > > sure how anyone could be expected to really *know* what the > > > answer to the original question was, which makes me wonder > > > if the question is really even a fair question to ask. > > > > > > Do we *know* that B is the case, or have we just always > > > *observed* that it *happens to be* the case? > > > > > > Best regards, > > > > > > Jonathan Gennick --- Brighten the corner where you are > > > http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] > > > > > > Join the Oracle-article list and receive one > > > article on Oracle technologies per month by > > > email. To join, visit > >http://four.pairlist.net/mailman/listinfo/oracle-article, > > > or send email to [EMAIL PROTECTED] and > > > include the word "subscribe" in either the subject or body. > > > > > > > > > > > > > > > MG> On 2004.01.06 13:44, Jay Wade wrote: > > > >> Hello: > > > >> > > > >> I was looking through some OCP questions posted on the web and came > >across > > > >> the one below. > > > >> I believe the answer is (D), because the join type would be dependent > >on the > > > >> number of rows within the table. Is this correct or does the > >OPTIMIZER_MODE > > > >> set to FIRST_ROWS alter this behavior? > > > >> > > > >> > > > >> The cost-based optimizer can choose between a nested loops join and a > >sort > > > >> merge join operation. All tables are analyzed and the OPTIMIZER_MODE > >is set > > > >> to FIRST_ROWS. Which execution plan will be the result? > > > >> > > > >> a. The sort-merge join. > > > >> > > > >> b. The nested loops join. > > > >> > > > >> c. This depends on some sort parameter values. > > > >> > > > >> d. This depends on the number of rows in each table. > > > >> > > > >> _________________________________________________________________ > > > >> Check your PC for viruses with the FREE McAfee online computer scan. > > > >> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 > > > >> > > > >> -- > > > >> Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > >> -- > > > >> Author: Jay Wade > > > >> INET: [EMAIL PROTECTED] > > > >> > > > >> Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > > >> San Diego, California -- Mailing list and web hosting services > > > >> --------------------------------------------------------------------- > > > >> 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). > > > >> > > > > > > MG> -- > > > MG> Mladen Gogala > > > MG> Oracle DBA > > > MG> -- > > > MG> Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Jonathan Gennick > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > > San Diego, California -- Mailing list and web hosting services > > > --------------------------------------------------------------------- > > > 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). > > > > > > >-- > >Mladen Gogala > >Oracle DBA > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.net > >-- > >Author: Mladen Gogala > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services -- 858-538-5051 http://www.fatcity.com > >San Diego, California -- Mailing list and web hosting services > >--------------------------------------------------------------------- > >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). > > _________________________________________________________________ > Enjoy a special introductory offer for dial-up Internet access ? limited > time only! http://join.msn.com/?page=dept/dialup > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jay Wade > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > 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). >
-- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
