it's possible that the index was small enough to stay cached in the SGA?
From: Miller, Jay [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Tuning question - Why did this index help so much?
Date: Wed, 25 Jul 2001 08:31:28 -0800
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
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
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
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
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
