CBO如何选择相同cost的索引

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> CBO如何选择相同cost的索引

CBO如何选择相同cost的索引

ACOUG年会杨长老的演讲中,曾提到一个问题,

一条SQL语句,两种执行计划的cost值相同,CBO是如何选择执行计划?

翻译一下问题,创建测试表数据,

SQL create table z (a number, b number); Table created.

SQL begin   2    for i in 1 .. 10000 loop   3      insert into z values(i, i);   4    end loop;   5    commit;   6  end;   7  / PL/SQL procedure successfully completed.

SQL create index idx_z_01 on z(a); Index created.

SQL create index idx_z_02 on z(b); Index created.

SQL exec dbms_stats.gather_table_stats('BISAL', 'Z', cascade=true); PL/SQL procedure successfully completed.

对于以下SQL,

select * from z where a=1 and b=1;

根据10053显示,可以看出,IDX_Z_01和IDX_Z_02这两个索引,cost相同,CBO会选择何种执行计划?

注:关于10053介绍,可以参考《》

Index Stats::   Index: IDX_Z_01  Col#: 1     LVLS: 1  #LB: 20  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 18.00   Index: IDX_Z_02  Col#: 2     LVLS: 1  #LB: 20  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 18.00

Access Path: index (AllEqRange)     Index: IDX_Z_01     resc_io: 2.00  resc_cpu: 14613     ix_sel: 0.000100  ix_sel_with_filters: 0.000100     Cost: 2.00  Resp: 2.00  Degree: 1   Access Path: index (AllEqRange)     Index: IDX_Z_02     resc_io: 2.00  resc_cpu: 14613     ix_sel: 0.000100  ix_sel_with_filters: 0.000100     Cost: 2.00  Resp: 2.00  Degree: 1

杨长老提到dbsnake曾经写过,检索了下,有一篇文章介绍,

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

文章总结来讲,

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

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

先验证(2)的观点,从上面10053可以看出,两个索引的cost相同,叶子块数相同,此时CBO选择的是IDX_Z_01,因为他的名字,排在IDX_Z_02前面,

Best:: AccessPath: IndexRange   Index: IDX_Z_01          Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.00  Bytes: 0

执行计划显示,使用索引IDX_Z_01,

CBO如何选择相同cost的索引

为了验证(1),人为设置索引IDX_Z_01的叶子块为100,

SQL exec dbms_stats.set_index_stats('BISAL', 'IDX_Z_01', numlblks=100);  PL/SQL procedure successfully completed.

10053显示,

Index Stats::   Index: IDX_Z_01  Col#: 1     LVLS: 1  #LB: 100  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 18.00   Index: IDX_Z_02  Col#: 2     LVLS: 1  #LB: 20  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 18.00

此时选择的是IDX_Z_02,因为IDX_Z_02的叶子块要小于IDX_Z_01(20100),

Best:: AccessPath: IndexRange   Index: IDX_Z_02          Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.00  Bytes: 0

执行计划显示,使用索引IDX_Z_02,

CBO如何选择相同cost的索引

除了索引名称,以及索引叶子块,本来我怀疑聚簇因子,会是另外一个影响因素,但通过设置IDX_Z_01的clsfct高于IDX_Z_02的clsfct,

exec dbms_stats.set_index_stats(‘BISAL’,’IDX_Z_01’,clstfct=21);

根据10053显示,IDX_Z_01的clsfct高于IDX_Z_02的clsfct,

Index Stats::   Index: IDX_Z_01  Col#: 1     LVLS: 1  #LB: 100  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 21.00   Index: IDX_Z_02  Col#: 2     LVLS: 1  #LB: 20  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 18.00

此时CBO选择的是IDX_Z_01,因为他的名字,排在IDX_Z_02前面,并未考虑聚簇因子的影响,

Best:: AccessPath: IndexRange   Index: IDX_Z_01          Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.00  Bytes: 0

总结:

对于cost相同的索引,10gR2及以上的版本,Oracle CBO还是有方法选择,索引叶子块是第一个条件,索引名称排序是第二个条件。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

CBO如何选择相同cost的索引 CBO如何选择相同cost的索引
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> CBO如何选择相同cost的索引


 上一篇
试用ODU软件恢复corrupt block 试用ODU软件恢复corrupt block
Oracle的数据恢复处理,有各种方法工具支持,在这方面,我算是一个新手,也是处于不断的学习中。 业界有一些著名的恢复软件,简单罗列一下, 1. Oracle DUL 是 Oracle公司内部的数据库恢复工具,由在荷兰的Oracle S
下一篇 
自适应log file sync影响案例 自适应log file sync影响案例
Oracle最吸引人的地方,就是有些答案,隐藏在种种现象之中,扑朔迷离,朦朦胧胧,就像侦探办案,首先要有思路,其次要有证据,再者就是扎实的基础知识,另外就是些运气。 例如最近碰见了一个案例,一套3节点11.2.0.4 RAC,某