RE: explain plan, can you explain this?

When doing an inefficient index range scan on a table, it's common to do
many more buffer gets than the number of blocks in the table & index -
that's why it's generally much more efficient to use a FTS if you will
be touching more than 5% of the table blocks (just a rule off thumb).
To understand why this is, you need to understand how an index range
scan works - check out the concepts guide.  Basically Oracle is
searching through the b-tree to the table block for each row, and has to
repeat that process for every row, even within the same block, so a
block with 100 rows will be "consistent read" 100 times, i.e. 100
buffer_gets of the same block.  Sounds like the CBO is expecting the
query to be much more selective (low cardinality) than it really is so
you might want to try a FULL hint and see if that works better, then
drill into the estimated cardinality and possibly a 10053 trace to see
why the CBO's calculations are going wrong.
 
Regards,
Brandon

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Dan Norris



So, if all the table data can be scanned in 128 blocks and even if you
do full scans on both indexes, there's only 168 blocks, then how can
this thing do 273 buffer gets per execution on average? 

Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.

Other related posts: