0

性能优化:复合索引的创建剖析

小鱼 2016-08-10
103


小鱼(邓秋爽)

云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化,SQL优化和troubleshooting



首先明确复合索引的创建思路:
1 前导列尽可能让更多的核心业务SQL能够使用
2 单个SQL语句索引的前导列尽量选择等值条件做为索引的前导列

这里我们如果在对in的谓词、三个条件的SQL语句复合索引的创建做一些更深入的分析,详细的例子如下:




select *+index(t09 ind_owner_type_objid)*/*

from t09 where owner=’SYS’ 

and object_type in (‘TABLE’,’INDEX’) 

and object_id>30000

and object_id<310000;


该sql语句需要在 owner、object_type、object_id 上创建复合索引,这个复合索引创建顺序如何,这里我们只考虑让该 sql 的执行计划最优秀,不用考虑别的SQL能够共用该索引,下面我们来看看两种复合索引的性能和执行计划。


索引(owner+object_type+object_id):


SQL> select *+index(t09 ind_owner_type_objid)*/* 

from t09 where owner='SYS' 

and object_type in ('TABLE','INDEX') 

and object_id>30000 

and object_id<310000;

60 rows selected.


我们来看执行计划




统计信息如下:


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed


再来看索引(object_type+owner+object_id):


SQL> select *+index(t09 ind_type_owner_objid)*/*

 from t09 where owner='SYS' 

 and object_type in ('TABLE','INDEX') 

 and object_id>30000 

 and object_id<310000;

60 rows selected.


执行计划如下:




统计信息如下:


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed


看出来对于where后面的谓词条件owner=’SYS’ and object_type in (‘TABLE’,’INDEX’) and object_id>30000 and object_id<310000,无论是选择(owner+object_type+object_id)还是(object_type+owner+object_id)的复合索引,优化器在索引范围扫描过程中都可以直接对谓词条件走access,而不需要走filter,这里优化器做了INLIST ITERATOR执行计划,这个类似一个oracle index skip range的执行计划,当对in条件中的第一组做index range scan后,会重新跳跃到分支块上再做index range scan,这个相比oracle的另一种执行计划CONCATENATION要更加高效点,因为不用再从根节点来重新走分支块最后到叶块。

如果我们优化器回到8I,这两个SQL的执行计划依然一样


SQL> select *+optimizer_features_enable('8.1.7') 

index(t09 ind_owner_type_objid)*/* 

from t09 

where owner='SYS' 

and object_type in ('TABLE','INDEX') 

and object_id>30000 

and object_id<310000;

60 rows selected.


我们来看执行计划




统计信息如下:


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed


继续看第二条SQL:


SQL> select *+optimizer_features_enable('8.1.7')

index(t09 ind_type_owner_objid)*/* 

from t09 

where owner='SYS‘

and object_type in ('TABLE','INDEX')

and object_id>30000 

and object_id<310000;

60 rows selected.


我们来看执行计划




统计信息如下:


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed



「喜欢文章,快来给作者赞赏墨值吧」
文章转载自小鱼,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部