记一次标量子查询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小时仍未执行完：

改写后的SQL：

select部分子查询没有了，后面增加了表的外关联，红色是改变的部分

select

......

case when (t71.acct_item_type_id is not null and t71.prd_inst_id is not null)

then t71.new_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,

(

select distinct prd_inst_id,acct_item_type_id,new_id

from dat_acct_item_21_201607 t7,

(select distinct old_acct_type_id,

max(to_number(new_acct_type_id)) over (partition by old_acct_type_id) as new_id

from PAR_ACCT_ACCT_REPLACE t9) t91

where t7.fee_type=1 and t7.acct_item_type_id=to_number(t91.old_acct_type_id) and

exists(select 1 from PAR_ACCT_OFR_RANGE t8 where t7.ofr_id=t8.ofr_id)

) t71

where t1.acct_item_type_id=t71.acct_item_type_id(+) and t1.prd_inst_id=t71.prd_inst_id(+);

下面是改写后的SQL执行情况，6.4分钟执行完

总结：

对于主查询返回结果集大的SQL，如果存在标量子查询，必须通过改写才能大幅提高效率，否则效率会非常低。有点改写相对简单，有的会很复杂，改写后需要仔细验证改写前后业务逻辑的一致性。

在12c的版本，少量简单的标量子查询会自动转换成表关联，但是大多数复杂一点的情况都做不到自动转换，像上面这张SQL的改写，优化器是不可能实现的。

对于写SQL代码的研发人员，有时写标量子查询的SQL好像在逻辑上简单一些，但是对性能的影响确实非常巨大的。而且优化器对很多复杂的标量子查询在谓词推进、查询转换等方面没有普通的SQL考虑得那么周全，建议尽量少用或不用标量子查询。

今天的例子是select 列表上的标量子查询，还有一种在where 后面出现的标量子查询，效率也是非常差，有机会再分享类似案例。

昨天的思考题请继续思考。