Oracle子查詢性能優化一例(2)

　　(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秒。

　　這樣速度就提高了很多，但少了去年同比的數據，那就分成兩個查詢!按這個思路下來，最后得到的過程如下(只保留主要部分)：

　　[sql] view plaincopycreateor 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秒，提高差不多三個數量級。

　　分析：

　　1) 在數據量大時，子查詢的性能會受到比較大的影響;

　　2) 在子查詢中使用了函數to_char(add_months(to_date(tb.rptdate,'yyyymm'),12),'yyyymm')，對大數據量查詢時，執行函數也會對系統產生比較大的影響。

原文轉自：http://www.anti-gravitydesign.com