优化前的SQL:
select *
from (select ta.fl_securityid,ta.fc_securitymarket,ta.fc_securitycode,ta.fl_investmenttypes,Fl_Balancetype,fl_circulation,
ta.fc_settlecurrency,
fpc_ValueDate(ta.fl_securityid,to_date(20161215,'yyyymmdd') ) fd_valuedate,
sum(ta.ff_quantity) ff_quantity,sum(ta.ff_originalint) ff_originalint,sum(ta.ff_baseint) ff_baseint,ta.fl_securitytypes
from (select a.fl_securityid,a.fc_securitymarket,a.fc_securitycode,a.fc_currency fc_settlecurrency,
a.ff_quantity,
a.ff_originalint,a.ff_baseint,
a.fl_securitytype fl_securitytypes,
a.fl_investmenttypes,a.Fl_Balancetype,
decode(a.fl_hsflag,1,7,1) fl_circulation
from tc_securitybalance a
where a.fl_assetid = 1438669
and a.fd_entrydate = to_date(20161215,'yyyymmdd') - 1
and a.fl_securitytype in(2,5)
) ta
group by ta.fl_securityid,ta.fc_securitymarket,ta.Fc_Securitycode,ta.fl_securitytypes,ta.fc_settlecurrency,fl_investmenttypes,Fl_Balancetype,fl_circulation
) tb
where tb.ff_originalint > 0
and fd_valuedate = to_date(20161215,'yyyymmdd')
子查询里面有个字段调用了函数 fpc_ValueDate,外层WHERE条件里面对这个字段进行了过滤。得到的执行计划如下:
可以看到,filter谓词中,外层的fd_valuedate = to_date(20161215,‘yyyymmdd’)被推入到了子查询中的INDEX RANGE SCAN,导致很慢,优化将这个条件放到内层的having中可以解决。
优化后:
select *
from (select ta.fl_securityid,ta.fc_securitymarket,ta.fc_securitycode,ta.fl_investmenttypes,Fl_Balancetype,fl_circulation,
ta.fc_settlecurrency,
fpc_ValueDate(ta.fl_securityid,to_date(20161215,'yyyymmdd') ) fd_valuedate,
sum(ta.ff_quantity) ff_quantity,sum(ta.ff_originalint) ff_originalint,sum(ta.ff_baseint) ff_baseint,ta.fl_securitytypes
from (select a.fl_securityid,a.fc_securitymarket,a.fc_securitycode,a.fc_currency fc_settlecurrency,
a.ff_quantity,
a.ff_originalint,a.ff_baseint,
a.fl_securitytype fl_securitytypes,
a.fl_investmenttypes,a.Fl_Balancetype,
decode(a.fl_hsflag,1,7,1) fl_circulation
from tc_securitybalance a
where a.fl_assetid = 1438669
and a.fd_entrydate = to_date(20161215,'yyyymmdd') - 1
and a.fl_securitytype in(2,5)
) ta
group by ta.fl_securityid,ta.fc_securitymarket,ta.Fc_Securitycode,ta.fl_securitytypes,ta.fc_settlecurrency,fl_investmenttypes,Fl_Balancetype,fl_circulation
having fpc_ValueDate(ta.fl_securityid,to_date(20161215,'yyyymmdd') ) = to_date(20161215,'yyyymmdd')
) tb
where tb.ff_originalint > 0
执行计划如下: