Thanks for the help. Do you have any ideas to the FIRST_ROWS behavior in regards to NL?
I've been looking and found
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#39473
it goes into detail about using hints for joins but now how the FIRST_ROWS forces a join selection on anything other then sample size. I'm interested to know more about the behavior and the links that I am finding don't seem to offer the depth I thought they would. Or maybe they do and I'm missing the boat....
From: "Ryan" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: OCP Question (Perf Tuning) Date: Tue, 06 Jan 2004 11:34:26 -0800
thought so, I'm not 100% certain the OCP will say that though. alot of inaccuracies in that test.
btw, Ive been playing with first_rows lately. I've noticed that it has a preference for 'INDEX FULL SCAN' over 'INDEX RANGE SCAN'. Ive found that in some test cases where you have two tables approximately 3m and 1.5m rows in size, that INDEX RANGE SCAN actually returns the first 25 or so records faster, than 'INDEX FULL SCAN', there by making FIRST_ROWS, inferior.
Surprised me. I've read some docs on this and I think that a range scan is always preferably when you only want a few rows? What am I missing? ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 06, 2004 1:59 PM
> Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to
all other
> methos despite the price.
>
> 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).
> >
>
> --
> 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).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan 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).
_________________________________________________________________
Working moms: Find helpful tips here on managing kids, home, work — and yourself. http://special.msn.com/msnbc/workingmom.armx
-- 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).
