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