深入内核：CBO对于Cost值相同索引的选择
崔华，网名 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_NAME LEAF_BLOCKS —————————— ———– A_IDX_T1 185 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_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;
从上述显示内容中我们可以看到，上述SQL的执行计划从之前的走对索引a_idx_t1的索引范围扫描变为了现在的走对索引b_idx_t1的索引范围扫描，这就验证了我们之前提到的结论：对于Oracle 10gR2及其以上的版本，如果Cost值相同的索引的叶子块数量不同，则Oracle会选择叶子块数量较少的那个索引。
近期文章
业务架构
电子渠道（网络销售）分析系统、数据治理
IT基础架构
分布式存储解决方案 | zData一体机 | 容灾环境建设
数据架构
Oracle DB2 MySQL NoSQL
专项服务：架构/安全/容灾/优化/整合/升级/迁移
运维服务：运维服务 代维服务
人才培养：个人认证 企业内训
软件产品：SQL审核、监控、数据恢复
应用架构
应用软件和中间件：数据建模 | SQL审核和优化 | 中间件服务