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

这里我们稍微讨论一下CBO对于Cost值相同的索引的选择,可能会有朋友认为在同样Cost的情况下,Oracle会按照索引名的字母顺序来选择索引,实际上并不完全是这样,CBO对于Cost值相同的索引的选择和Oracle的版本有关

   

MOS上文章“Handling of equally ranked (RBO) or costed (CBO) indexes [ID 73167.1]”明确指出——When the CBO detects 2 indexes that cost the same, it makes the decision based on the following:

Ÿ     (up to release 9.2.06) indexes ascii name so that index ‘AAA’ would be chosen over index  ‘ZZZ’. See Bug 644757

Ÿ     (starting with 9.2.0.7 and in 10gR1) bigger NDK for fully matched indexes (not for fast full scans). See Bug 2720661

Ÿ     (in 10gR2 and above) index with lower number of leaf blocks. See Bug 6734618

   

这意味着对于Oracle 10gR2及其以上的版本,CBO对于Cost值相同的索引的选择实际上会这样:

1、    如果Cost值相同的索引的叶子块数量不同,则Oracle会选择叶子块数量较少的那个索引;

2、    如果Cost值相同的索引的叶子块数量相同,则Oracle会选择索引名的字母顺序在前面的那个索引。

 

这个非常容易验证,我们来看一个实例:

在一个11.2.0.3的环境中创建一个测试表T1

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Connected as nbs

 

SQL> create table t1 as select * from dba_objects;

 

Table created

 

T1增加一列object_id_1,并将其值修改成和列object_id的值一致:

SQL> alter table t1 add (object_id_1 number);

 

Table altered

 

SQL> update t1 set object_id_1=object_id;

 

83293 rows updated

 

SQL> commit;

 

Commit complete

 

分别在列object_id和列object_id_1上创建名为a_idx_t1b_idx_t1B树索引:

SQL> create index a_idx_t1 on t1(object_id);

 

Index created

 

SQL> create index b_idx_t1 on t1(object_id_1);

 

Index created

 

对表T1收集一下统计信息:

SQL> exec dbms_stats.gather_table_stats(ownname => ‘NBS’, tabname => ‘T1’, estimate_percent => 100, cascade => TRUE, no_invalidate => false);

 

PL/SQL procedure successfully completed

 

此时索引a_idx_t1b_idx_t1的统计信息显然是完全一致的(这意味着走这两个索引的同类型执行计划的Cost值会相同),从如下查询结果中我们可以看到,它们的叶子块的数量均为185

SQL> select index_name,leaf_blocks from dba_indexes where table_owner=’NBS’ and table_name=’T1′;

 

INDEX_NAME                     LEAF_BLOCKS

—————————— ———–

A_IDX_T1                               185

B_IDX_T1                               185

 

在当前情形下,如果我们执行目标SQLselect * from t1 where object_id=1000 and object_id_1=1000,显然此时Oracle既可以走索引a_idx_t1也可以走索引b_idx_t1

 

从如下查询结果中我们可以看到,此时Oracle选择了走索引a_idx_t1

SQL> set autotrace traceonly explain

SQL> select * from t1 where object_id=1000 and object_id_1=1000;

 

执行计划

———————————————————-

Plan hash value: 3036955902

 

—————————————————————————————-

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————————-

|   0 | SELECT STATEMENT            |          |     1 |   103 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |   103 |     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_ID_1″=1000)

   2 – access(“OBJECT_ID”=1000)

    这就验证了我们之前提到的结论——对于Oracle 10gR2及其以上的版本,如果Cost值相同的索引的叶子块数量相同,则Oracle会选择索引名的字母顺序在前面的那个索引。

   

现在我们把索引b_idx_t1的叶子块数量从之前的185改为现在的184

SQL> exec dbms_stats.set_index_stats(ownname => ‘NBS’, indname => ‘B_IDX_T1’, numlblks => 184);

 

PL/SQL procedure successfully completed

 

从如下查询结果中我们可以看到,上述改动生效了:

SQL> select index_name,leaf_blocks from dba_indexes where table_owner=’NBS’ and table_name=’T1′;

 

INDEX_NAME                     LEAF_BLOCKS

—————————— ———–

A_IDX_T1                               185

B_IDX_T1                               184

 

然后我们再次执行上述目标SQL

SQL> select * from t1 where object_id=1000 and object_id_1=1000;

 

执行计划

———————————————————-

Plan hash value: 606792237

 

—————————————————————————————-

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————————-

|   0 | SELECT STATEMENT            |          |     1 |   103 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |   103 |     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_ID_1″=1000)

        从上述显示内容中我们可以看到,上述SQL的执行计划从之前的走对索引a_idx_t1的索引范围扫描变为了现在的走对索引b_idx_t1的索引范围扫描,这就验证了我们之前提到的结论——对于Oracle 10gR2及其以上的版本,如果Cost值相同的索引的叶子块数量不同,则Oracle会选择叶子块数量较少的那个索引。

 

    至于RBO对于Cost值相同的索引的选择,我在我新书《基于OracleSQL优化》的第一章 Oracle里的优化器中会详细描述,这里不再赘述。


4 Comments on “CBO对于Cost值相同的索引的选择”

  1. lt says:

    基于Oracle的SQL优化一书的样章能否提供在线阅读?


Leave a Reply

Your email address will not be published. Required fields are marked *