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).
