oracle中复合索引的创建剖析―包含in的三个条件SQL语句复合索引的创建
之前文章中提过复合索引的创建思路：
1 前导列尽可能让更多的核心业务SQL能够使用
2 单个SQL语句索引的前导列尽量选择等值条件做为索引的前导列
这里我们如果在对in的谓词、三个条件的SQL语句复合索引的创建做一些更深入的分析，详细的例子如下：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> create table t09 as select * from dba_objects;
Table created.
SQL> create index ind_owner_type_objid on t09(owner,object_type,object_id);
Index created.
SQL> create index ind_type_owner_objid on t09(object_type,owner,object_id);
Index created.
SQL> set autotrace traceonly;
SQL> analyze table t09 compute statistics for all indexes;
Table analyzed.
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.
Execution Plan
----------------------------------------------------------
Plan hash value: 1730993038
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 5 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T09 | 1 | 207 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_OWNER_TYPE_OBJID | 1 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER"='SYS' AND ("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE') AND
"OBJECT_ID">30000 AND "OBJECT_ID"<310000)
Note
-----
- dynamic sampling used for this statement (level=2)
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.
Execution Plan
----------------------------------------------------------
Plan hash value: 1925664837
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 5 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T09 | 1 | 207 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_TYPE_OWNER_OBJID | 1 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE') AND "OWNER"='SYS' AND
"OBJECT_ID">30000 AND "OBJECT_ID"<310000)
Note
-----
- dynamic sampling used for this statement (level=2)
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.
Execution Plan
----------------------------------------------------------
Plan hash value: 1730993038
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 4 |
| 1 | INLIST ITERATOR | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T09 | 1 | 207 | 4 |
|* 3 | INDEX RANGE SCAN | IND_OWNER_TYPE_OBJID | 1 | | 3 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER"='SYS' AND ("OBJECT_TYPE"='INDEX' OR
"OBJECT_TYPE"='TABLE') AND "OBJECT_ID">30000 AND "OBJECT_ID"<310000)
Note
-----
- cpu costing is off (consider enabling it)
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> 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.
Execution Plan
----------------------------------------------------------
Plan hash value: 1925664837
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 4 |
| 1 | INLIST ITERATOR | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T09 | 1 | 207 | 4 |
|* 3 | INDEX RANGE SCAN | IND_TYPE_OWNER_OBJID | 1 | | 3 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE') AND
"OWNER"='SYS' AND "OBJECT_ID">30000 AND "OBJECT_ID"<310000)
Note
-----
- cpu costing is off (consider enabling it)
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
