複合索引與函式索引優化一例

導語：業務上有很多應用需求都是date型別轉換成char型別進行比較求值。這樣的應用，使用索引往往就要應用函式索引，本文講一例函式索引的應用。

SELECT
MAX(tt.workitem_id) workitem_id,
tt.task_id
FROM
doudou tt
WHERE
tt.position_id=5
AND TO_CHAR(tt.pos_rcv_datim, 'yyyymmdd')='20140815'
GROUP BY
tt.task_id;

670 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3539805324
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |  1862 | 91238 |  4570   (1)| 00:00:55 |
|   1 |  HASH GROUP BY     |        |  1862 | 91238 |  4570   (1)| 00:00:55 |
|*  2 |   TABLE ACCESS FULL| DOUDOU |  1864 | 91336 |  4569   (1)| 00:00:55 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TT"."POSITION_ID"=5 AND
              TO_CHAR(INTERNAL_FUNCTION("TT"."POS_RCV_DATIM"),'yyyymmdd')='20140815')

Statistics
----------------------------------------------------------
         93  recursive calls
          0  db block gets
      16855  consistent gets
      16637  physical reads
          0  redo size
      35014  bytes sent via SQL*Net to client
       1007  bytes received via SQL*Net from client
         46  SQL*Net roundtrips to/from client
         47  sorts (memory)
          0  sorts (disk)
        670  rows processed
 
IMAGING@doudou1> create index idx_doudou on doudou(TO_CHAR(pos_rcv_datim, 'yyyymmdd'),POSITION_ID) online nologging;
Index created.

IMAGING@doudou1> 
SELECT
MAX(tt.workitem_id) workitem_id,
IMAGING@doudou1>   2    3  tt.task_id
  4  FROM
  5  doudou tt
  6  WHERE
  7  tt.position_id=5
  8  AND TO_CHAR(tt.pos_rcv_datim, 'yyyymmdd')='20140815'
  9  GROUP BY
 10  tt.task_id;
670 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3748972397
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  1862 | 87514 |   167   (1)| 00:00:03 |
|   1 |  HASH GROUP BY               |            |  1862 | 87514 |   167   (1)| 00:00:03 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DOUDOU     |  1864 | 87608 |   166   (0)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | IDX_DOUDOU |   746 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(TO_CHAR(INTERNAL_FUNCTION("POS_RCV_DATIM"),'yyyymmdd')='20140815'
              AND "TT"."POSITION_ID"=5)

Statistics
----------------------------------------------------------
         25  recursive calls
          0  db block gets
        191  consistent gets
          5  physical reads
          0  redo size
      35014  bytes sent via SQL*Net to client
       1007  bytes received via SQL*Net from client
         46  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        670  rows processed
670 rows selected.

總結：
1.業務需求中這種型別的轉換，開發應和DBA多多進行交流，往往就是一個小問題引起系統的崩潰。儘量減少型別轉換。
2.複合索引與函式索引這種結合也是可以考慮的。自己暗喜一下。
 
附表：
收集多列統計資訊
begin
dbms_stats.gather_table_stats (
ownname         => 'DOUDOU',
tabname         => 'DOUDOU',
estimate_percent=> 100,
method_opt  => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS  (pos_rcv_datim,POSITION_ID)',
cascade         => TRUE
);
end;
/

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