Oracle数据库中基本的查询优化与子查询优化讲解(2)-木庄网络博客

那问题应该是在fetch对应的查询上，把

v_sql :='select rptdate, jcdlb, jcdlbName,sum(nvl(runtime,0)) sumruntime, sum(nvl(buhegeTime,0)) buhegeTime,count(jcdcount) jcdcount, 
 
    sum(nvl(tongbiRuntime,0)) tongbiRuntime, sum(nvl(tongbiBhg,0))tongbiBhg from ( 
 
  select rptdate, runtime,r.chaoshangxian+r.chaoxiaxian buhegeTime, r.objid, 1 jcdcount, 
 
    (select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiRuntime, 
 
    (select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiBhg, 
 
    (select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb, 
 
    (select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbName 
 
    from ywtj_rpt_dyhgl_month r where rptdate = :rptYear) 
 
  group by rptdate, jcdlb,  jcdlbName  
 
  order by rptdate ,jcdlb ';      --①

单独拿出来执行，大约5.7秒多。
再把其中的子查询：

selectrptdate, runtime, r.chaoshangxian+r.chaoxiaxian buhegeTime, r.objid, 1jcdcount, 
 
    (select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiRuntime, 
 
    (select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiBhg, 
 
    (select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb, 
 
    (select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbName 
 
    from ywtj_rpt_dyhgl_month r where rptdate =:rptYear;  --②

单独拿出来执行，速度很快，但返回的数据量很大。如果再对其循环分析，估计性能也好不到哪去，先行放弃这种尝试。

把查询②中的tongbiRuntime和tongbiBhg两个字段子查询去掉，执行速度更快，同样再把①中的tongbiRuntime和tongbiBhg两个字段子查询去掉，执行速度提高很多，时间约0.157秒。

这样速度就提高了很多，但少了去年同比的数据，那就分成两个查询！按这个思路下来，最后得到的过程如下（只保留主要部分）：

createor replace procedure Rpt_Dyhgl_year( 
--电压合格率-年 
 v_rptyear      in varchar2, --年yyyy 
 --rpttype      in varchar2,  --报表类型，1年报，2季报 
 ref_cursor      out sys_refcursor --返回游标 
) 
as 
 -- 变量声明 
 ....... 
begin 
  delete from YWTJ_RPT_DYHGL_YEAR where RPTYEAR = v_rptyear and RPTTYPE = '1'; 
  commit; 
 --查询原始数据 
 v_sql := 'select rptdate, jcdlb, jcdlbName, sum(nvl(runtime,0)) sumruntime,sum(nvl(buhegeTime,0)) buhegeTime, count(jcdcount) jcdcount 
    from ( select rptdate, runtime,r.chaoshangxian+r.chaoxiaxian buhegeTime, r.objid, 1 jcdcount, 
    (select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb, 
    (select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbName 
    from ywtj_rpt_dyhgl_month r where rptdate = :rptYear) 
  group by rptdate, jcdlb,  jcdlbName 
  order by rptdate ,jcdlb '; 
  -- 插入新数据 
  v_sqlRptInsert := 'Insert IntoYWTJ_RPT_DYHGL_YEAR (RPTYEAR,RPTDATE,RPTTYPE) values(:RPTYEAR,:RPTDATE,:RPTTYPE)'; 
  v_sqlRptUpdate := ''; 
 
  -- 计算1--12月的当月数据 
  for rptmonth in 1..12 loop 
   -- 打开游标开始计算合格率 
   open v_cur for v_sql using v_rptyear||LPAD(rptmonth, 2, '0'); 
   loop 
     fetch v_cur intov_rptDate,v_jcdlb,v_jcdLbName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount; 
     exit when v_cur%notfound; 
     --计算合格率 
     if v_sumRuntime <> 0 then 
      v_hgl := (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime *100; 
     end if; 
     -- 更新当月数据 
     ........ 
    end loop; 
    close v_cur; 
   -- 打开游标开始计算同比合格率 
   open v_cur for v_sql using (v_rptyear-1)|| LPAD(rptmonth, 2, '0'); 
   loop 
     fetch v_cur into v_rptDate,v_jcdlb,v_jcdLbName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount; 
     exit when v_cur%notfound; 
     --计算合格率 
     if v_sumRuntime <> 0 then 
      v_hgl := (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime*100; 
     end if; 
     -- 更新同比数据 
     ......... 
    end loop; 
    close v_cur; 
  end loop; 
 
  -- 更新累计数据 
  .........  
  -- 计算综合合格率 
  ........ 
 
  open ref_cursor for select * fromYWTJ_RPT_DYHGL_YEAR r where r.rptyear = v_rptyear and r.rpttype = 1 order byr.rptdate; 
end Rpt_Dyhgl_year;

经验证，执行速度大约在一秒多，也就是说性能从原来的900多秒提高到1秒，提高差不多三个数量级。

标签：Oracle

返回前面的内容