on 10.2.0.5 AIX
The query is really simple just querying a table filtered by combination of columns, the table has 2 indexes (each 1 column) which is as part of where clause. from the plan, oracle seems to deal with just only cost, ya it is true cost is cheaper than by scanning only 1 index, but IO cost would likely double (scanning 2 indexes, bitmap and operation and than conver bitmap to rowid).
---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 112 | 66 (2)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID | T_4 | 1 | 112 | 66 (2)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS | | | | | | | 3 | BITMAP AND | | | | | | | 4 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 5 | INDEX RANGE SCAN | IDX_1 | 25237 | | 7 (0)| 00:00:01 | | 6 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 7 | INDEX RANGE SCAN | IDX_0 | 25237 | | 40 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- here by force to use index 1, ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 112 | 501 (1)| 00:00:07 | |* 1 | TABLE ACCESS BY INDEX ROWID| T_4 | 1 | 112 | 501 (1)| 00:00:07 | |* 2 | INDEX RANGE SCAN | IDX_1 | 3801 | | 7 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------
it seems the cost is higher than bitmap conversion, but from the real execution time the second one is really faster. it seems cost calculation on the first plan is misleading.
Advertisement
