Oracle資料庫中基本的查詢最佳化與子查詢最佳化講解_oracle

1. 查詢條件合理排序

Oracle採用自下而上的順序解析WHERE字據，從最佳化效能角度考慮，建議將那些可以過濾掉大量記錄行的條件寫在WHERE子句的末尾，而將表

之間的串連條件置於其他WHERE子句之前，即對易排查的條件先做判斷處理，這樣在過濾掉儘可能多的記錄後再進行等值串連，可以提高檢索效率。

例如：

SELECT empno, ename, job, sal, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.deptno = 20;

要比下述語句的查詢效率高一些：

SELETE empno, ename, job, sal, dept.deptno, dnameFROM emp, deptWHERE emp.deptno = 20 AND emp.deptno = dept.deptno;

2.串連中使用表別名

在進行串連查詢時，建議在SQL語句中使用表的別名， 並把別名首碼與每個欄位上。這樣可以減少解析的時間，並可避免因欄位名存在歧義（使用多個

表中出現的同名欄位）而導致的語法錯誤。例如下述語句：

SELECT e.empno, e.ename, e.job, e.sal, e.deptno, d.nameFROM emp e, dept dWHERE e.deptno = d.deptno AND e.deptno = 20;

3.用EXISTS替換DISTINCT

在進行一對多關聯性的表間串連查詢時，如果要剔除結果中的重複行，可以考慮使用EXISTS（結合子查詢）替換DISTINCT。

例如：

SELECT deptno, dname FROM dept d WHERE EXIST (SELECT 'y' FROM emp e WHERE e.deptno = d.deptno);

4.用WHERE替換HAVING

由於SELECT語句的執行順序為：先ＷＨＥＲＥ子句, 在GROUP BY 子句， 然後SELECT查詢， 再後HAVINT子句，最後是ORDER BY子句，因此在進行分組

查詢時，如果過濾條件不涉及分組計算，則應該使用WHERE語句替換HAVING指定的過濾條件

例如：

SELECT deptno, avg(sal) FROM empWHERE deptno IN (10, 20)GROUP BY deptno;

效率會高於下面

SELECT deptno, avg(sal) FROM empWHERE deptno IN (10, 20)GROUP BY deptno;

當然， 如果分組查詢的過濾條件設計分組計算，就只能在HAVING子句中指定了。

5.子查詢最佳化

1） 在資料量大時，子查詢的效能會受到比較大的影響；

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

下面來看一個例子：

createor replace procedure Rpt_Dyhgl_year(  rptyear      in varchar2, --年yyyy  --rpttype      in varchar2,  --報表類型，1年報，2季報  ref_cursor      out sys_refcursor --返回遊標 ) As  -- 變數聲明  。。。。。 begin   delete from YWTJ_RPT_DYHGL_YEAR where RPTYEAR = rptyear and RPTTYPE = '1';   commit;  --查詢未經處理資料  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 ';   -- 插入新資料   v_sqlRptInsert := 'Insert IntoYWTJ_RPT_DYHGL_YEAR (RPTYEAR,RPTDATE,RPTTYPE) values(:RPTYEAR,:RPTDATE,:RPTTYPE)';   v_sqlRptUpdate := '';   -- 計算1--12月的當月資料   for rptmonth in 1..12 loop    execute immediate v_sqlRptInsertusing rptyear, rptmonth, '1';    commit;    open v_cur for v_sql using rptyear ||LPAD(rptmonth, 2, '0');    loop      fetch v_cur intov_rptDate,v_jcdlb,v_jcdName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount,v_tongbiRuntime,v_tongbiBhg;      exit when v_cur%notfound;      --計算合格率      。。。。。。      -- 更新當月資料      。。。。。。     end loop;     close v_cur;   end loop;   -- 更新累計資料   。。。。。。   commit;   -- 計算綜合合格率   open ref_cursor for select * fromYWTJ_RPT_DYHGL_YEAR r where r.rptyear = rptyear and r.rpttype = 1 order byr.rptdate; end Rpt_Dyhgl_year; 

功能運行正常，查詢時間大約30-40秒。

然後，把報表部署到運行環境中後，驗證功能時，發現2、3分鐘報表沒有出來！多次嘗試，後來終於出來結果了：940多秒，15分鐘多！這樣使用者肯定是不能接受的！使用pl/sql dev的效能分析對Rpt_Dyhgl_year的執行過程進行分析，發現幾乎所有的時間都化在了

fetch v_cur into v_rptDate,v_jcdlb,v_jcdName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount,v_tongbiRuntime,v_tongbiBhg;

（期間把過程轉成程式執行，效能分析時發現，主要時間都是由dr.Read()佔用，和對過程的分析一致。）

那問題應該是在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秒，提高差不多三個數量級。