小鱼（邓秋爽）
云和恩墨专家，有超过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
