复合索引性能问题初探

在《品悟性能优化》一书，4.4.3章节里介绍了复合索引的两个特点：前缀性，可选性。何为前缀性，该书阐述为排除skip scan index的情况（索引前置列的取值不多，如性别），约束条件如果不包含复合索引的第一列，则该复合索引不会被用到；何为可选性，该书阐述为字段值越多，可选性越强，定位记录越少，查询效率越高。即查询返回记录少的列应该放在复合索引的前面。

而在《收获不止oracle》一书，5.2.1.9章节里也介绍了复合索引的两个特点：①在等值查询的情况下，复合索引的列无论那一列在前，性能都一样。②复合索引的两列，当一列是范围查询，一列是等值查询的情况下，等值查询列在前，范围查询列在后，这样的索引才是高效的。

根据上述对复合索引两位作者的不同见解之处，我们通过测试，辨别下实事的真相。测试环境是11.2.0.3单实例，oracle linux 5.4

SQL> create table t as select * from dba_objects;

Table created.

SQL> select count(*) from t;

  COUNT(*)
----------
    109971

SQL> select count(distinct object_type) from t;

COUNT(DISTINCTOBJECT_TYPE)
--------------------------
                        45

SQL> select count(distinct object_id) from t;

COUNT(DISTINCTOBJECT_ID)
------------------------
                  109971

SQL> create index ind_t_obj_id on t(object_id,object_type);

Index created.

SQL> create index ind_t_obj_ty on t(object_type,object_id);

Index created.

SQL> select /*+ index(t,ind_t_obj_ty) */ * from T where object_id=5585 and object_type='TABLE';

Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    11 |  2277 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |    11 |  2277 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_OBJ_TY |     2 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID"=5585)

Note
-----
   - dynamic sampling used for this statement (level=2)

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

SQL> select /*+ index(t,ind_t_obj_id) */ * from T where object_id=5585 and object_type='TABLE';

Execution Plan
----------------------------------------------------------
Plan hash value: 607336433

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    11 |  2277 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |    11 |  2277 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_OBJ_ID |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=5585 AND "OBJECT_TYPE"='TABLE')

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1622  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
我们这里看到等值查询结果是不一样的，证实了《品悟》一书的可选性是正确的。

SQL> select /*+ index(t,ind_t_obj_ty) */ * from T where object_id > 20  and object_id < 2000 and object_type='TABLE';

488 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   912 |   184K|    49   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |   912 |   184K|    49   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_OBJ_TY |   912 |       |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID">20 AND "OBJECT_ID"<2000)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         85  consistent gets
          0  physical reads
          0  redo size
      51220  bytes sent via SQL*Net to client
        872  bytes received via SQL*Net from client
         34  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        488  rows processed

SQL> select /*+ index(t,ind_t_obj_id) */ * from T where object_id > 20  and object_id < 2000 and object_type='TABLE';

488 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 607336433

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   912 |   184K|    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |   912 |   184K|    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_OBJ_ID |     9 |       |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID">20 AND "OBJECT_TYPE"='TABLE' AND "OBJECT_ID"<2000)
       filter("OBJECT_TYPE"='TABLE')

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         87  consistent gets
          0  physical reads
          0  redo size
      51220  bytes sent via SQL*Net to client
        872  bytes received via SQL*Net from client
         34  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        488  rows processed
        
结果，不言而喻。以上两个sql在不用hint的时候，CBO都会自动选择走IND_T_OBJ_ID。所以，《收获不止oracle》一书的结论有待商榷（如果不是我测试有问题的话）。