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 <mgogala_at_adelphia.net>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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
>(mgogala_at_adelphia.net) 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:jonathan@gennick.com
> >
> > 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 Oracle-article-request_at_gennick.com 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: fish_dba_at_hotmail.com
> > >>
> > >> 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: 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).
> > >>
> >
> > 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: jonathan_at_gennick.com
> >
> > 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: 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).
> >
>
>--
>Mladen Gogala
>Oracle DBA
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Mladen Gogala
> INET: mgogala_at_adelphia.net
>
>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: 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).
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: fish_dba_at_hotmail.com
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: 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).
Received on Tue Jan 06 2004 - 16:14:26 CST
