Re: Tuning question - Why did this index help so much?

"Miller, Jay" wrote:
>
> 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...

My guess is that it may have something to do with the dispersion of data. It would have been interesting to see how the first case would have performed if you had just rebuilt the index. It may be, I have no special info about it but it would make sense to me, that index range scans behave like full table scans, that the high-water-mark of index b1 was higher than it should have been for a reason or another and so the range scan was reading more blocks than necessary. Add to this that the information was probably neatly concentrated in a clean, freshly built b2 index. A little bit of this plus a little bit of that ...

-- 
Regards,

Stephane Faroult
Oriole Corporation
Voice:  +44  (0) 7050-696-269 
Fax:    +44  (0) 7050-696-449 
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).