最近有几个朋友都遇到了因为标量子查询(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 后面出现的标量子查询，效率也是非常差，有机会再分享类似案例。
昨天的思考题请继续思考。
