记一次标量子查询SQL改写

 最近有几个朋友都遇到了因为标量子查询(scalar subquery)导致SQL性能奇差的问题，前几天刚好给客户改写了一个，拿出来与大家分享。



SQL（省略了select部分其他简单列，红色部分即标量子查询，返回了acct_item_type_id字段）：

原sql是一个create table语句，我们这里分析select部分就好

select

......

case

when

exists(select 1 from dat_acct_item_21_201607 t7

where t1.prd_inst_id=t7.prd_inst_id and t7.fee_type=1 and

exists(select 1 from PAR_ACCT_OFR_RANGE t8

where t7.ofr_id=t8.ofr_id) and

exists(select 1 from PAR_ACCT_ACCT_REPLACE t9

where t7.acct_item_type_id=to_number(t9.old_acct_type_id)) and

t1.acct_item_type_id=t7.acct_item_type_id)

then

(select max(to_number(t6.new_acct_type_id)) from PAR_ACCT_ACCT_REPLACE t6 where t1.acct_item_type_id=to_number(t6.old_acct_type_id))

else t1.acct_item_type_id

end acct_item_type_id

......

from

(select a.acct_item_id,a.cust_Id,a.acct_id,

case

when b.type_level =0 then 0

else a.prd_inst_id

end prd_inst_id,pay_flag,a.source_inst_id,a.fee_type,a.ofr_id,a.group_id,a.event_pricing_strategy_id,

case

when a.acct_item_type_id =465 then 40000

when a.acct_item_type_id =11050 then 40025

when a.acct_item_type_id =22 then 40020

when a.acct_item_type_id =23 then 40021

else a.acct_item_type_id

end acct_item_type_id,charge,a.counts,a.calls,flux,duration,billing_duration,billing_cycle_id,fee_cycle_id,pay_acct_id,pay_method

from

(select acct_item_id,cust_Id,acct_id,prd_inst_id,pay_flag,source_inst_id,fee_type,ofr_id,group_id,event_pricing_strategy_id,

acct_item_type_id,charge,counts,calls,flux,duration,billing_duration,billing_cycle_id,fee_cycle_id,pay_acct_id,pay_method

from dat_acct_item_21_201607 where fee_load_condition_id=0 and fee_out_condition_id=0 and pay_acct_id not in('71616471632','71616471631')

) a,

(select distinct type_level,acct_item_type_id from hss.tb_bil_acct_item_type@hssdb) b

where a.acct_item_type_id=b.acct_item_type_id

) t1;



sql monitor中显示的执行计划，8.5小时仍未执行完：