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