I have the following query:
select col1, col2
from table
where primary_key = :bind_variable
Table has 56 rows, clustering factor on the PK_INDEX is 53. So not good. I get the following plan:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 2 (50)|
| 1 | TABLE ACCESS BY INDEX ROWID| table| 1 | 33 | 2 (50)|
|* 2 | INDEX UNIQUE SCAN | table_PK | 56 | | |
-------------------------------------------------------------------------------------
standard unique index scan retrieving 1 record. This uses 63 Logical IOs. I assume its high because of my high clustering factor. Though I am not sure why so many LIOs if I am getting just 1 row with a unique scan even with a bad clustering factor.
If I change the query as follows:
select col1, col2
from table
where primary_key = :bind_variable
order by primary_key
I get the example same query plan, but just 4 logical IOs. Any idea why logical IOs drops so much even though the plan does not change?
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Aug 12 2004 - 13:06:28 CDT
