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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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.

--
http://www.freelists.org/webpage/oracle-l