子查询中使用了递归CONNECT BY巨慢的解决方案

有一段sql执行速度很慢 要50s

经查询是由于在sql的子查询中使用了递归查询导致

sql如下

select 
                  (SELECT to_char(temp.rwwcsx, 'yyyy-mm-dd')
                from (SELECT * FROM fxyd_rwdy_mx mx order by mx.rwdy_sj desc) temp
               WHERE temp.fxxx_bh in (F_GET_SJ_FXXX_BH(t.fxxx_bh))
                       --(SELECT a.fxxx_bh
                       --   FROM fxyd_rwmx a
                       --  start with a.fxxx_bh = t.fxxx_bh
                       -- CONNECT BY a.fxxx_bh = PRIOR a.sj_fxxx_bh)
                 and temp.hxcl_dm = 'RWTC'
                 and temp.rwtczt_dm = 'YXF'
                 and temp.jsjg_dm = '23407000000'
                 and rownum = 1) sj_xf_rwwcsx, --取本机关的上级下发的完成时限
             (SELECT jg.swjgcclx_bj
                FROM wd_swjg jg
               WHERE jg.swjg_dm =
                   (SELECT pc.dyry_swjg_dm
                      from (SELECT * FROM fxyd_rwdy_mx mx order by mx.rwdy_sj desc) temp,
                           fxyd_rwpc pc
                     WHERE temp.fxxx_bh in (F_GET_SJ_FXXX_BH(t.fxxx_bh))
                       --(SELECT a.fxxx_bh
                       --   FROM fxyd_rwmx a
                       --  start with a.fxxx_bh = t.fxxx_bh
                       -- CONNECT BY a.fxxx_bh = PRIOR a.sj_fxxx_bh)--原因同上
                       and temp.rwpc_bh = pc.rwpc_bh
                       and temp.hxcl_dm = 'RWTC'
                       and temp.rwtczt_dm = 'YXF'
                       and temp.jsjg_dm = '23407000000'
                       and rownum = 1)) sj_xf_jgcc --取上级下发机关的层次类型
          from FXYD_RWMX t, fxyd_ydgx_hxcl_pz hxcl, FXYD_YDGX_PZ gx
         where gx.xxly_dm(+) = t.xxly_dm
           and gx.zg_swjg_dm(+) = t.nsr_swjg_dm
           and gx.FXDJ_DM(+) = t.DJ_ZZ
           and hxcl.ydfs_dm(+) = gx.ydfs_dm
           and hxcl.dy_swjg_dm(+) = '23407000000'
           and t.jsgzzt_dm='02' 
                   and t.rwlx_dm = 'RW' 
                   and t.jsjg_dm = '23407000000' 
                   and (t.gbbz_dm is null or t.gbbz_dm = 'N') --不是被归并的信息
                   and t.rwtczt_dm = 'WDY' --WDY 包括上级下发，本级产生，下级退回，本级收回，重新应对
                   and not exists (SELECT 1 FROM fxyd_nsrbmd_lb lb WHERE lb.nsrdzdah = t.nsrdzdah)--纳税人在白名单中的过滤掉
                   and t.xxly_dm = '01'          
                   
                   order by t.nsrdzdah,t.tsjg_dm --按照推送机关排序时因为归并时，等级和应对方式

 由于一定要使用子查询递归 因此想了一个方法 将子查询的递归放到一个自己写function中 结果查询只用了10秒

create or replace function F_GET_SJ_FXXX_BH(FXXX_BH varchar2) return varchar2 is
  TYPE ref_type IS REF CURSOR;
  V_FXXX_BHS varchar2(2000);
  V_SQL varchar2(2000);
  V_INDEX number;
  V_TEMP_FXXX_BHS varchar2(2000);
  cur ref_type;
begin
  V_SQL:='SELECT a.fxxx_bh FROM fxyd_rwmx a start with a.fxxx_bh = '''||FXXX_BH||''' CONNECT BY a.fxxx_bh = PRIOR a.sj_fxxx_bh';
  --dbms_output.put_line(V_SQL);
  open cur for V_SQL;
  V_INDEX:=0;
  FETCH cur into V_TEMP_FXXX_BHS;
  while cur%FOUND LOOP
  IF V_INDEX > 0 THEN
      V_FXXX_BHS:=V_FXXX_BHS||','||V_TEMP_FXXX_BHS;
    ELSE
      V_FXXX_BHS:=''||V_TEMP_FXXX_BHS;
  END IF;
  V_INDEX:=V_INDEX+1;
  FETCH cur into V_TEMP_FXXX_BHS;
  END LOOP;
  CLOSE cur;
  V_FXXX_BHS:=V_FXXX_BHS||'';
  --dbms_output.put_line(V_FXXX_BHS);
  return V_FXXX_BHS;
end F_GET_SJ_FXXX_BH;

 从这里可以看出 虽然function实现的和原先的sql是一样的 ，但是每次循环改用function单独去计算 会比直接写在sql语句中节省很多时间（注：这个纯个人意见，个人对sql研究不是很深入，有更好的办法希望大家能回复我，不断学习中）