ITPub博客

SQL优化] 避免在WHERE子句中对列使用函数

原创 Linux操作系统 作者:yxyup 时间:2007-06-08 00:00:00 0 删除 编辑
前两天在系统的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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7364032/viewspace-17463/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-06

  • 博文量
    164
  • 访问量
    1314170