圈内好友有一个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语句和执行计划如下：
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;
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更多是个实际的动手的东西。
原文地址：关于包含函数表达式的复合索引优化查询, 感谢原作者分享。