Hi Tom,
That's why I expected a 25% decrease in processing time (instead of reading
2 index blocks and 2 table blocks it read 2 index blocks and 1 table block).
But why would it give a 75% decrease?
Jay
-----Original Message-----
Sent: Wednesday, July 25, 2001 2:29 PM
To: Multiple recipients of list ORACLE-L
I think it's because the optimizxer did not have to go to the table b to
satisfy the query - it went to the index only.
does this make sense?
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, July 25, 2001 12:57 PM
To: Multiple recipients of list ORACLE-L
it's possible that the index was small enough to stay cached in the SGA?
>From: "Miller, Jay" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Tuning question - Why did this index help so much?
>Date: Wed, 25 Jul 2001 08:31:28 -0800
>
>The other week a new production process was running much more slowly than
>anticipated. A file needed to be sent out by 6:00pm and at the rate the
>table was being populated it wouldn't complete until around 9:30pm. The
>production people and developers came to me for help and I saw that the
>SQL
>Explain Plan that was usually being executed (this would run a few million
>times) was something like
>
>select a.col1,a.col2,a.col3,b.col2
>from a, b
>where a.col4=b.col1
>and a.col5=:b1
>
>nested loops
> table a
> index a1 (unique)
> table b
> index b1 (range)
>
>This looked pretty good, but it occurred to me that only one column was
>being selected from table b, so if I added a index (b2) that combined col1
>and col2 to table b then it wouldn't be necessary to read table b at all,
>all the information would be in index b2.
>
>This resulted in a plan of:
>
>nested loops
> table a
> index a1 (unique)
> index b1 (range)
>
>I did so on the fly (this was only a 4,000 row table so it took almost no
>time to create the index). I anticipated that it would cut about 25% off
>the processing time (only 3/4 as many block reads). Instead it cut about
>75% off the processing time causing it to finish at 5:45 (I was a hero to
>the developers and production people, but had to warn them not to tell
>their
>management about it since I could get in trouble for not following the
>Change Control Process).
>
>My question is, where did the additional 50% efficiency come from? What am
>I missing? I'm glad it worked so well, but would like to understand why...
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Miller, Jay
> INET: [EMAIL PROTECTED]
>
>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: [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).
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
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: [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.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
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: [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.com
--
Author: Miller, Jay
INET: [EMAIL PROTECTED]
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: [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).
