前两天在系统的TOP SQL LIST中发现有这样一条SQL，拿了出来分析了一下。发现这条SQL的WHERE条件中对列使用了函数，这样会影响SQL性能。由于简单改写了一下性能大有好转。详细如下
原SQL
SQL> select contract_idntfr,plan_code,process_date,cvrg_idntfr
2 from PRODUSRTB.ffuactmthmv
3 where to_char(ac_ymth,'yyyymm') ='200704'
4 and (event in ('ContractSurrender','ContractLapseProcessing','StartPremiumHoliday') or
5 (event = 'ContractStatusChange' and after_value = 'PremiumHoliday'))
6* and PRODUSR.get_pol_ymth_area(contract_idntfr,'200704') between '001001000000000000000' and '001001000999999999999'
5261 rows selected.
Elapsed: 00:01:29.17
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13059 Card=4 Bytes=2
64)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FFUACTMTHMV' (Cost=13059
Card=4 Bytes=264)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 INDEX (RANGE SCAN) OF 'IDX_FFUACTMTHMV_EVENT' (NON
-UNIQUE) (Cost=344)
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'IDX_FFUACTMTHMV_EVENT' (NON
-UNIQUE) (Cost=344)
8 3 BITMAP CONVERSION (FROM ROWIDS)
9 8 INDEX (RANGE SCAN) OF 'IDX_FFUACTMTHMV_EVENT' (NON
-UNIQUE) (Cost=344)
10 3 BITMAP CONVERSION (FROM ROWIDS)
11 10 INDEX (RANGE SCAN) OF 'IDX_FFUACTMTHMV_EVENT' (NON
-UNIQUE) (Cost=344)
Statistics
----------------------------------------------------------
20915 recursive calls
0 db block gets
378354 consistent gets
9064 physical reads
1100 redo size
148545 bytes sent via SQL*Net to client
4505 bytes received via SQL*Net from client
352 SQL*Net roundtrips to/from client
20908 sorts (memory)
0 sorts (disk)
5261 rows processed
修改后SQL
SQL> select contract_idntfr,plan_code,process_date,cvrg_idntfr
2 from PRODUSRTB.ffuactmthmv
3 where ac_ymth between to_date('2007040100:00:00') and to_date('2007043023:59:00') /*或者使用大于，小于*/
4 and (event in ('ContractSurrender','ContractLapseProcessing','StartPremiumHoliday') or
5 (event = 'ContractStatusChange' and after_value = 'PremiumHoliday'))
6 and PRODUSR.get_pol_ymth_area(contract_idntfr,'200704') between '001001000000000000000' and '001001000999999999999'
7 ;
5261 rows selected.
Elapsed: 00:00:06.40
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2066 Card=9 Bytes=59
4)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FFUACTMTHMV' (Cost=2066
Card=9 Bytes=594)
2 1 INDEX (RANGE SCAN) OF 'IDX_FFUACTMTHMV_YMTH' (NON-UNIQUE
) (Cost=447 Card=134802)
Statistics
----------------------------------------------------------
20908 recursive calls
0 db block gets
296607 consistent gets
0 physical reads
1040 redo size
148515 bytes sent via SQL*Net to client
4505 bytes received via SQL*Net from client
352 SQL*Net roundtrips to/from client
20908 sorts (memory)
0 sorts (disk)
5261 rows processed