复合索引与函数索引优化一例

复合索引与函数索引优化一例

导语：业务上有很多应用需求都是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;
/
来自群组: Oracle DBA交流