[[163836]]

　　崔华，网名 dbsnake

　　Oracle ACE Director，ACOUG 核心专家

　　编辑手记：感谢崔华授权我们独家转载其精品文章，也欢迎大家向“Oracle”社区投稿。

　　这里我们稍微讨论一下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_t1和b_idx_t1的B树索引：

　　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_t1和b_idx_t1的统计信息显然是完全一致的(这意味着走这两个索引的同类型执行计划的Cost值会相同)，从如下查询结果中我们可以看到，它们的叶子块的数量均为185：

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

　　INDEX_NAMELEAF_BLOCKS

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

　　A_IDX_T1185

　　B_IDX_T1 185

　　在当前情形下，如果我们执行目标SQL：

　　“select * 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;

　　这就验证了我们之前提到的结论——对于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_NAMELEAF_BLOCKS

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

　　A_IDX_T1185

　　B_IDX_T1184

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

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

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