ITPub博客

首页 > 数据库 > Oracle > CBO如何选择相同cost的索引

CBO如何选择相同cost的索引

原创 Oracle 作者:bisal 时间:2018-12-10 12:47:50 0 删除 编辑

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介绍,可以参考《 探索索引的奥秘 - 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值相同的索引的选择》

http://www.dbsnake.net/handle-equally-costed-indexes.html


文章总结来讲,

对于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,


为了验证(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(20<100),

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


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


除了索引名称,以及索引叶子块,本来我怀疑聚簇因子,会是另外一个影响因素,但通过设置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还是有方法选择,索引叶子块是第一个条件,索引名称排序是第二个条件。

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

请登录后发表评论 登录
全部评论
Oracle ACE,10g/11g OCP,11g OCM,国内首批Oracle YEP成员(Oracle Young Expert Program,Oracle用户组年轻专家项目),EXIN DevOps Master,Oracle爱好者,微信公众号:bisal的个人杂货铺

注册时间:2013-07-26

  • 博文量
    340
  • 访问量
    2623856