常數複合索引應用案例

從一個客戶的真實最佳化案例引申的問題。

客戶的一個資料庫需要進行最佳化，不過由於程式開發方沒有介入，因此這次最佳化無法對SQL進行修改。

僅對資料庫級的調整一般來說收效不大，不過發現客戶資料庫中個別的SQL存在效能問題，且這個效能問題已經影響到整個資料庫。如果可以將這個SQL最佳化，那麼可以解決目前資料庫的效能問題。幸運的是，這個問題可以透過新增索引來進行最佳化。

模擬問題SQL如下：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 – Production

SQL> create table t (id number not null, created date, other char(200));

Table created.

SQL> insert into t select rownum, created, 'a' from all_objects;

31126 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'T')

PL/SQL procedure successfully completed.

SQL> var v_id number
SQL> var v_date varchar2(14)
SQL> explain plan for
  2  select count(*)
  3  from t
  4  where nvl(created, sysdate) > to_date(:v_date, 'yyyymmddhh24miss') - 3
  5  and id = :v_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    13 |    92 |
|   1 |  SORT AGGREGATE      |             |     1 |    13 |       |
|*  2 |   TABLE ACCESS FULL  | T           |     1 |    13 |    92 |
--------------------------------------------------------------------

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

   2 - filter(NVL("T"."CREATED",SYSDATE@!)>TO_DATE(:Z,'yyyymmddhh24miss')-3 AND "T"."ID"=TO_NUMBER(:Z))

Note: cpu costing is off

16 rows selected.

對於這個SQL，透過索引方式最佳化很簡單，只需要建立ID和CREATED上的複合索引，就可以避免全表掃描：

SQL> create index ind_t_id_created on t (id, created);

Index created.

SQL> explain plan for
  2  select count(*)
  3  from t
  4  where nvl(created, sysdate) > to_date(:v_date, 'yyyymmddhh24miss') - 3
  5  and id = :v_id;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

--------------------------------------------------------------------------
| Id  | Operation            |  Name             | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     1 |    13 |     2 |
|   1 |  SORT AGGREGATE      |                   |     1 |    13 |       |
|*  2 |   INDEX RANGE SCAN   | IND_T_ID_CREATED  |     1 |    13 |     2 |
--------------------------------------------------------------------------

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

   2 - access("T"."ID"=TO_NUMBER(:Z))
       filter(NVL("T"."CREATED",SYSDATE@!)>TO_DATE(:Z,'yyyymmddhh24miss')-3)

Note: cpu costing is off

17 rows selected.

Oracle之所以可以選擇索引掃描，是由於複合索引中CREATED列為空的記錄也會被儲存。由於ID列為非空，而索引不會儲存所有列全為空的情況，因此CREATED為空的記錄同樣可以在索引中找到。

事實上，即使ID不為空，由於另一個查詢條件指定了ID = :V_ID，這使得訪問的記錄並不包括ID為空的記錄，這使得複合索引仍然可以包括這個SQL需要訪問的所有資料。

不過新的疑問來了，如果查詢的SQL不包含ID列的限制條件，則目前的索引不在可用：

SQL> alter table t modify id null;

Table altered.

SQL> explain plan for
  2  select count(*)
  3  from t
  4  where nvl(created, sysdate) > to_date(:v_date, 'yyyymmddhh24miss') - 3;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     8 |    92 |
|   1 |  SORT AGGREGATE      |             |     1 |     8 |       |
|*  2 |   TABLE ACCESS FULL  | T           |  1556 | 12448 |    92 |
--------------------------------------------------------------------

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

   2 - filter(NVL("T"."CREATED",SYSDATE@!)>TO_DATE(:Z,'yyyymmddhh24miss')-3)

Note: cpu costing is off

16 rows selected.

針對這種情況，一種方法是找一個不為空的欄位做聯合索引，而更省空間的方法是建立複合常數索引：

SQL> create index ind_t_created0 on t(created, 0);

Index created.

SQL> explain plan for
  2  select count(*)
  3  from t
  4  where nvl(created, sysdate) > to_date(:v_date, 'yyyymmddhh24miss') - 3;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id  | Operation             |  Name           | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     1 |     8 |     4 |
|   1 |  SORT AGGREGATE       |                 |     1 |     8 |       |
|*  2 |   INDEX FAST FULL SCAN| IND_T_CREATED0  |  1556 | 12448 |     4 |
-------------------------------------------------------------------------

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

   2 - filter(NVL("T"."CREATED",SYSDATE@!)>TO_DATE(:Z,'yyyymmddhh24miss')-3)

Note: cpu costing is off

16 rows selected.

最終透過建立一個包含常數的複合索引，從而避免了這個SQL的全表掃描。