Oracle谓词推入导致sql很慢的优化

优化前的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 

执行计划如下：