深入理解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                                111IDX_T_B                                111hr@ORCL> set autot trace exphr@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 offhr@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                                111IDX_T_B                                110/* Cost 值相同、CBO  選擇葉子塊數量較少的索引*/hr@ORCL> set autot trace exphr@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