深入内核: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会选择叶子块数量较少的那个索引。

近期文章

成就卓越:云和恩墨大讲堂期刊第三期

新年贺礼:云和恩墨大讲堂期刊第二期

删繁就简-云和恩墨的一道面试题解析

用SQL解一道数学题:Gauss和Poincare

新年贺礼:云和恩墨大讲堂期刊发行

2015 Oracle 十大热门文章精选

Oracle 12c ASM 防火防盗新特性揭秘

DBA入门之路:学习与进阶之经验谈

DBA入门之路:关于日常工作的建议

业务架构

电子渠道(网络销售)分析系统、数据治理

IT基础架构

分布式存储解决方案 | zData一体机 | 容灾环境建设

数据架构

Oracle DB2 MySQL NoSQL

专项服务:架构/安全/容灾/优化/整合/升级/迁移

运维服务:运维服务 代维服务

人才培养:个人认证 企业内训

软件产品:SQL审核、监控、数据恢复

应用架构

应用软件和中间件:数据建模 | SQL审核和优化 | 中间件服务

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-03-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

一条delete语句的调优(r4笔记第86天)

今天刚上上班,就接到客户的邮件,说生产环境中执行某一条delete sql语句的时间超过了3个小时。最后客户无奈取消了这次数据清理,准备今天在申请时间重做。所以...

3345
来自专栏杨建荣的学习笔记

一个SQL性能问题的优化探索(二)(r11笔记第38天)

继续前几天的一个案例一个SQL性能问题的优化探索(一)(r11笔记第33天) 如下的SQL语句存在索引字段CARD_NO,但是执行的时候却走了全表扫描,因为这是...

3388
来自专栏数据和云

Oracle智能之SQL诊断:SQL Tuning Advisor推荐执行计划

编辑手记:在前一段,一篇智能数据库优化的论文引起广泛的关注,其实在 Oracle 数据库中,已经引入了大量自动化和智能化的方法去进行自动调节,包括在 SQL 层...

2755
来自专栏张善友的专栏

开源数据库PostgreSQL发布了v9.2版

PostgreSQL是一种著名的开源数据库。最近PostgreSQL全球开发小组发布了最新的9.2版本,对性能做出了极大提升,并增加了对JSON的内建支持。 早...

1725
来自专栏大数据

爬虫无烦恼,大牛教你怎么简单爬36kr网,带源码

抓取36kr网站数据 爬虫代码和数据sql脚本在下方链接,抓取过程主要是抓包找到url递归解析的规律,三个主要的函数 以及 表结构脚本如下: #建表语句(写爬虫...

2119
来自专栏简书专栏

mysql必知必会

数据库是按照数据结构来组织、存储和管理数据的仓库,它产生于距今。 六十多年前,随着信息技术和市场的发展,特别是二十世纪九十年代以后,数据管理不再仅仅是存储和管...

351
来自专栏Greenplum

Greenplum使用TPC-H测试过程及结果

TPC-H 基准测试是由 TPC-D(由 TPC 组织于 1994 年指定的标准,用于决策支持系统方面的测试基准)发展而来的.TPC-H 用 3N...

2326
来自专栏Rgc

提高查询数据速度

在实际项目中,通过设计表架构时,设计系统结构时,查询数据时综合提高查询数据效率 1.适当冗余 数据库在设计时遵守三范式,同时业务数据(对数据的操作,比如资料...

3538
来自专栏Keegan小钢

App项目实战之路(六):数据库篇

上一篇文章[服务端篇]提到本项目的数据库采用了关系型的 MySQL,那么,本文将基于 MySQL 聊聊本项目的数据库设计。

703
来自专栏杨建荣的学习笔记

增量数据丢失的原因分析(二)(r8笔记第76天)

今天处理的一个问题比较绕,花费了我不少的时间来分析,当然最后发现是拜拜忙碌一场空,还是有一些历史原因。 大体的环境情况如下,有一台线上库OLTP,其中也有会自己...

2618

扫码关注云+社区