Kirti made a similar suggestion. But I had done a select count(*) on the
table so that all the table rows would be loaded into memory. I suppose
that the index blocks might not have been, but even there the likelihood
that any given one of the million plus reads wouldn't find one of the 4,000
rows in memory seems rather small.
Hmm, is it possible to Cache an index? I just tried an ALTER INDEX xxx
CACHE; command and it didn't work.
Jay Miller
-----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: 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).
