云和恩墨：深入内核CBO对于Cost值相同索引的选择

最新推荐文章于 2025-12-07 20:52:38 发布

转载最新推荐文章于 2025-12-07 20:52:38 发布 · 132 阅读

文章标签：

#运维 #数据库

　　崔华，网名 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会选择叶子块数量较少的那个索引。

如何加入"云和恩墨大讲堂"微信群

搜索盖国强（Eygle）微信号：eeygle，或者扫描下面二维码，备注：云和恩墨大讲堂，即可入群。每周与千人共享免费技术分享，与讲师在线讨论。

云和恩墨数据驱动，成就未来。整合业界顶尖的技术与合作伙伴资源，围绕数据及相关领域，提供解决方案和专业服务。（业务联系电话: 01059003186-8019 ）业务架构电子渠道（网络销售）分析系统、数据治理 IT基础架构分布式存储解决方案 | zData一体机 | 容灾环境建设数据架构 Oracle DB2 MySQL NoSQL 专项服务：架构/安全/容灾/优化/整合/升级/迁移运维服务：运维服务代维服务人才培养：个人认证企业内训软件产品：SQL审核、监控、数据恢复应用架构应用软件和中间件：数据建模 | SQL审核和优化 | 中间件服务

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29715045/viewspace-2056912/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29715045/viewspace-2056912/