使用WITH语法优化SQL一则

下面这个SQL执行时间需要1.1小时：

selectround(t1.baodan / 10000, 1) "name1",

       decode(t2.baodan,       0,

              '--',

              round(t1.baodan / t2.baodan *100, 2) - 100) || '%' name2,

       round(t3.baodan / 10000, 1)"name3",

       decode(t4.baodan,

              0,

              '--',

              round(t3.baodan / t4.baodan *100, 2) - 100) || '%' name4,

       round(t5.baodan / 10000, 1)"name5"

  from (select nvl((d2.baodan - d1.ybaodan), 0)baodan

          from (selectnvl(sum(c.total_agree_premium), 0) ybaodan

frompams_auto_tempsave_extend  a,apply_base_info c

                 where ( a.biz_apply_policy_no=c.apply_policy_noor a.force_apply_policy_no=c.apply_policy_no )

                   and c.created_date <trunc(sysdate - 1)

                   andtrunc(c.insurance_begin_time) < trunc(sysdate - 1)

                   and c.policy_no is not null

                            ) d1,

               (selectnvl(sum(c.total_agree_premium), 0) baodan

 from pams_auto_tempsave_extend a,apply_base_info c

                 where (a.biz_apply_policy_no=c.apply_policy_no ora.force_apply_policy_no=c.apply_policy_no )

                   and c.created_date <trunc(sysdate)

                   andtrunc(c.insurance_begin_time) < trunc(sysdate)

                   and c.policy_no is not null

                             ) d2

              ) t1,

       (select nvl((d2.baodan - d1.ybaodan), 0)baodan

          from (selectnvl(sum(c.total_agree_premium), 0) ybaodan

 from pams_auto_tempsave_extend a,apply_base_info c