Oracleインデックスの詳細理解(17):Cost値が同じCBOによるインデックスの選択

ルールは次のとおりです.
                     
テストは次のとおりです.

hr@ORCL> drop table t purge;

Table dropped.
  
hr@ORCL> create table t as select * from dba_objects;

Table created.

hr@ORCL> alter table t add (object_id_1 number);

Table altered.

hr@ORCL> update t set object_id_1=object_id;

50363 rows updated.

hr@ORCL> commit;

Commit complete.

hr@ORCL> create index idx_t_a on t(object_id);

Index created.

hr@ORCL> create index idx_t_b on t(object_id_1);

Index created.

/*  、 Cost */
hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);

PL/SQL procedure successfully completed.

/*  、CBO */
hr@ORCL> select index_name,leaf_blocks from user_indexes where table_name='T' and index_name in ('IDX_T_A','IDX_T_B');

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
IDX_T_A                                111
IDX_T_B                                111

hr@ORCL> set autot trace exp
hr@ORCL> select * from t where object_id=1000 and object_id_1=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1194865126

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

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

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

/*  idx_t_b 111 110*/
hr@ORCL> set autot off
hr@ORCL> exec dbms_stats.set_index_stats(ownname=>'HR',indname=>'IDX_T_B',numlblks=>110);

PL/SQL procedure successfully completed.

hr@ORCL> select index_name,leaf_blocks from user_indexes where table_name='T' and index_name in ('IDX_T_A','IDX_T_B');

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
IDX_T_A                                111
IDX_T_B                                110

/* Cost  、CBO   */
hr@ORCL> set autot trace exp
hr@ORCL> select * from t where object_id=1000 and object_id_1=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3073359464

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

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

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

                    By David Lin                     20113-06-05                     Good Luck