SQL優化] 避免在WHERE子句中對列使用函式

yxyup發表於2007-06-08
SQL優化函式
前兩天在系統的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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章