CBO对于Cost值相同的索引的选择

ian@IAN> create table t1 as select * from dba_objects;
Table created.
Elapsed: 00:00:01.82
ian@IAN> alter table t1 add (object_id1 number);
Table altered.
Elapsed: 00:00:00.61
ian@IAN> update t1 set object_id1=object_id;
72681 rows updated.
Elapsed: 00:00:10.88
ian@IAN> commit;
Commit complete.
Elapsed: 00:00:00.00
ian@IAN> create index a_idx_t1 on t1(object_id);
Index created.
Elapsed: 00:00:01.26
ian@IAN> create index b_idx_t1 on t1(object_id1);
Index created.

ian@IAN> exec dbms_stats.gather_table_stats(user,TABNAME=>'T1',ESTIMATE_PERCENT=>100,CASCADE=>TRUE,NO_INVALIDATE=>FALSE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.41

ian@IAN> select index_name,leaf_blocks from user_indexes where table_name='T1';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
B_IDX_T1                               161
A_IDX_T1                               161

Elapsed: 00:00:00.13

ian@IAN> set autot traceonly exp
ian@IAN> select * from t1 where object_id=1000 and object_id1=1000;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 86048563

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |   102 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |   102 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | A_IDX_T1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID1"=1000)
   2 - access("OBJECT_ID"=1000)

ian@IAN> exec dbms_stats.set_index_stats(ownname=>'IAN',INDNAME=>'B_IDX_T1',NUMLBLKS=>151);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

ian@IAN> select index_name,leaf_blocks from user_indexes where table_name='T1';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
B_IDX_T1                               151
A_IDX_T1                               161

Elapsed: 00:00:00.02

ian@IAN> select * from t1 where object_id=1000 and object_id1=1000;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 821240183

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |   102 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |   102 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | B_IDX_T1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=1000)
   2 - access("OBJECT_ID1"=1000)