| 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?
Jay,
I'm guessing here, but maybe because *most* of the index was already in memory. Further, skipping the table blocks is a huge payoff - think of skipping disk access for all of the rows involved in the query. A simple 25% reduction does not really work here.
That is the theory behind the IOT (Index Only Table) structure - eliminate the extra disk access to speed things up. You just proved it in another way! :)
I can't prove anything that I say here, but it makes sense to me.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, July 25, 2001 3:21 PM
To: Multiple recipients of list ORACLE-L
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" <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: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us 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 - 13:27:34 CDT
 |
 |