关于包含函数表达式的复合索引优化查询

圈内好友有一个sql语句需要优化，sql语句和执行计划如下： SELECT 2 MAX(tt.workitem_id) workitem_id, 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; 67

圈内好友有一个sql语句需要优化，sql语句和执行计划如下：<br> SELECT<br> 2 MAX(tt.workitem_id) workitem_id,<br> 3 tt.task_id<br> 4 FROM<br> 5 doudou tt<br> 6 WHERE<br> 7 tt.position_id =5<br> 8 AND TO_CHAR(tt.pos_rcv_datim, 'yyyymmdd') =20140815<br> 9 GROUP BY<br> 10 tt.task_id;<code>圈内好友有一个sql语句需要优化，sql语句和执行计划如下：<br> SELECT<br> 2 MAX(tt.workitem_id) workitem_id,<br> 3 tt.task_id<br> 4 FROM<br> 5 doudou tt<br> 6 WHERE<br> 7 tt.position_id =5<br> 8 AND TO_CHAR(tt.pos_rcv_datim, 'yyyymmdd') =20140815<br> 9 GROUP BY<br> 10 tt.task_id;

670 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3539805324

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1797 | 88053 | 4570 (1)| 00:00:55 |
| 1 | HASH GROUP BY | | 1797 | 88053 | 4570 (1)| 00:00:55 |
|* 2 | TABLE ACCESS FULL| DOUDOU | 1800 | 88200 | 4569 (1)| 00:00:55 |
-----------------------------------------------------------------------------

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

2 - filter("TT"."POSITION_ID"=5 AND
TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("TT"."POS_RCV_DATIM"),'yyyymmdd'))=2
0140815)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16633 consistent gets
16630 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

看出这里走的全表扫描，可能一般朋友能否去掉TO_CHAR，建立(pos_rcv_datim,position_id)的复合索引，但是这个sql是不允许修改的，那么复合索引没办法了吗，其实不然我们是可以建立包含函数表达式的复合索引的

create index ind_doudou04 on doudou(TO_CHAR(tt.pos_rcv_datim, 'yyyymmdd'),position_id)然后再看最新的执行计划：

Execution Plan
----------------------------------------------------------
Plan hash value: 1864030226

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|

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

| 0 | SELECT STATEMENT | | 1862 | 87514 | 50 (2)| 00:00:01|

| 1 | HASH GROUP BY | | 1862 | 87514 | 50 (2)| 00:00:01|

|* 2 | INDEX RANGE SCAN| IND_DOUDOU4 | 1864 | 87608 | 49 (0)| 00:00:01|

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

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

2 - access(TO_CHAR(INTERNAL_FUNCTION("POS_RCV_DATIM"),'yyyymmdd')='201
40815' AND "TT"."POSITION_ID"=5)
filter("TT"."POSITION_ID"=5)

Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
62 consistent gets
52 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

上面这个sql的优化并不难，而这里小鱼想展示的是，可能平常我们所看见的复合索引多半是(col1,col2,…coln) 这类，很少有创建包含（col1,func(col,func_name)）这类复合索引，有些东西不要想当然以为可能或者不可能，oracle更多是个实际的动手的东西。

670 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3539805324 -----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1797 | 88053 | 4570 (1)| 00:00:55 |
| 1 | HASH GROUP BY | | 1797 | 88053 | 4570 (1)| 00:00:55 |
|* 2 | TABLE ACCESS FULL| DOUDOU | 1800 | 88200 | 4569 (1)| 00:00:55 |
----------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("TT"."POSITION_ID"=5 AND
TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("TT"."POS_RCV_DATIM"),'yyyymmdd'))=2
0140815) Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
16633 consistent gets
16630 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 看出这里走的全表扫描，可能一般朋友能否去掉TO_CHAR，建立(pos_rcv_datim,position_id)的复合索引，但是这个sql是不允许修改的，那么复合索引没办法了吗，其实不然我们是可以建立包含函数表达式的复合索引的 create index ind_doudou04 on doudou(TO_CHAR(tt.pos_rcv_datim, 'yyyymmdd'),position_id)然后再看最新的执行计划： Execution Plan
----------------------------------------------------------
Plan hash value: 1864030226 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time| -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1862 | 87514 | 50 (2)| 00:00:01| | 1 | HASH GROUP BY | | 1862 | 87514 | 50 (2)| 00:00:01| |* 2 | INDEX RANGE SCAN| IND_DOUDOU4 | 1864 | 87608 | 49 (0)| 00:00:01| -------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access(TO_CHAR(INTERNAL_FUNCTION("POS_RCV_DATIM"),'yyyymmdd')='201
40815' AND "TT"."POSITION_ID"=5)
filter("TT"."POSITION_ID"=5) Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
62 consistent gets
52 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 上面这个sql的优化并不难，而这里小鱼想展示的是，可能平常我们所看见的复合索引多半是(col1,col2,…coln) 这类，很少有创建包含（col1,func(col,func_name)）这类复合索引，有些东西不要想当然以为可能或者不可能，oracle更多是个实际的动手的东西。 原文地址：关于包含函数表达式的复合索引优化查询, 感谢原作者分享。