Tuning question - Why did this index help so much?

From: Miller, Jay <JayMiller_at_tdwaterhouse.com>
Date: Wed, 25 Jul 2001 08:49:15 -0700
Message-ID: <F001.00354AC6.20010725083128@fatcity.com>

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). Received on Wed Jul 25 2001 - 10:49:15 CDT