複合索引效能問題初探

在《品悟效能最佳化》一書，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》一書的結論有待商榷（如果不是我測試有問題的話）。