| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tuning question - Why did this index help so much?
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" <JayMiller_at_TDWaterhouse.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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: JayMiller_at_TDWaterhouse.com
>
>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: 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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: carmichr_at_hotmail.com 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: JayMiller_at_TDWaterhouse.com 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: 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 Wed Jul 25 2001 - 12:10:45 CDT
 |
 |