复合索引与函数索引优化一例
导语:业务上有很多应用需求都是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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-1257559/