索引優化index skip scan

上午碰到一個sql，執行計劃使用了Index skip scan，頭兒讓我看一下能否優化，大致資訊如下：
select t.ID,
       t.DO_ID,
       t.DO_CODE,
       t2.CODE,
        *****
        ******            
from justin1 t left join justin2 t2 on t. STAFF_ID = t2.ID
       where
       t.ERROR_STATE = :1 and t.RECEIVE_STATION_ID = :2
       and t.CONSIGNEE_ID = :3
       and (t.BELONG = :4 or t.BELONG = :5)
       and (t.DO_STATE = :6 or t.DO_STATE = :7)
       and (t.FINANCE_STATE = :8 or t.FINANCE_STATE = :9)
       and (t.DELIVER_STAT E = :10 or t.DELIVER_STATE = :11)
       and (t.DELIVER_TYPE = :12 or t.DELIVER_TYPE = :13)
       and t.BACK_ORDER_LOCK is null；
執行計劃
Operation                         |  Name                        |  Rows | Bytes|  Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT                  |                              |       |      |   1045 |       |       |
| HASH JOIN OUTER                   |                              |    43 |   15K|   1045 |       |       |
|  TABLE ACCESS BY INDEX ROWID      |justin1              |    43 |   13K|   1039 |       |       |
|  TABLE ACCESS FULL                |justin2                     |   648 |   31K|      5 |       |       |
|   INDEX SKIP SCAN                 |IDX_justin1_RCMD4COL      |     2K|      |    313 |       |   
首先檢視一下表justin1上各個欄位的選擇率：
SQL> select count(*),count(distinct f.driver_order),count(distinct f.receive_station_id),count(distinct f.consignee_id),count(distinct f.more_boxes) from justin1 f;

  COUNT(*) COUNT(DISTINCT F.DRIVER_ORDER) COUNT(DISTINCTF.RECEIVE_STATIO COUNT(DISTINCTF.CONSIGNEE_ID) COUNT(DISTINCTF.MORE_BOXES)
---------- ----------------------------- ------------------------------ ----------------------------- ---------------------------
   1017576                           321                             40                             5                           2

SQL> select count(distinct t.error_state),count(distinct t.do_state),count(distinct t.belong)  from justin1 t;

COUNT(DISTINCTT.ERROR_STATE) COUNT(DISTINCTT.DO_STATE) COUNT(DISTINCTT.BELONG)
---------------------------- ------------------------- -----------------------
                           2                         3                       7

SQL> select count(distinct t.finance_state),count(distinct t.deliver_state),count(distinct t.deliver_type)  from justin1 t;

COUNT(DISTINCTT.FINANCE_STATE) COUNT(DISTINCTT.DELIVER_STATE) COUNT(DISTINCTT.DELIVER_TYPE)
------------------------------ ------------------------------ -----------------------------
                             4                              5                             2
可以看到justin1總共有  1017576   條記錄，但是選擇率最高的欄位DRIVER_ORDER卻只有321個distinct值，選擇率奇差無比，只能選擇建立組合索引。
再來看看使用到的索引IDX_justin1_RCMD4COL的結構，建立在以下四個欄位上(DRIVER_ORDER, RECEIVE_STATION_ID, CONSIGNEE_ID, MORE_BOXES)。
而出現問題的sql的where條件中卻沒有引用到DRIVER_ORDER列，故會執行計劃中會出現index skip scan。
現在考慮建立一個新的組合索引，依據where條件中出現的列以及其選擇率，最終新建索引為,
SQL> alter session enable resumable;

Session altered

SQL> alter session set workarea_size_policy=manual;

Session altered

SQL> alter session set sort_area_size=104857600;

Session altered

SQL> alter session set db_file_multiblock_read_count = 128;

Session altered

SQL> create index IDX_justin1__RCMD3COL on justin1 (RECEIVE_STATION_ID, CONSIGNEE_ID, belong)
  2    tablespace justin1 online;
注意: 此為線上庫，一定要加online關鍵字。

再看執行計劃，cost從1045變為202，執行時間也從12秒縮減至3秒，鑑於該sql每小時執行一次，個人覺得此索引還是有新增的必要；
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                          |    88 | 32296 |   202   (1)| 00:00:03 |
|*  1 |  HASH JOIN OUTER              |                          |    88 | 32296 |   202   (1)| 00:00:03 |
|   2 |   INLIST ITERATOR             |                          |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| justin1         |    88 | 27896 |   196   (0)| 00:00:03 |
|*  4 |     INDEX RANGE SCAN          | IDX_Tjustin1_RCMD3COL |   684 |       |     4   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL           | justin2                |   652 | 32600 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-688356/，如需轉載，請註明出處，否則將追究法律責任。