小说排行榜完结版,盗墓笔记小说txt下载,盗墓笔记

主頁 > 知識(shí)庫 > Oracle數(shù)據(jù)庫中基本的查詢優(yōu)化與子查詢優(yōu)化講解

Oracle數(shù)據(jù)庫中基本的查詢優(yōu)化與子查詢優(yōu)化講解

1. 查詢條件合理排序

Oracle采用自下而上的順序解析WHERE字據(jù)，從優(yōu)化性能角度考慮，建議將那些可以過濾掉大量記錄行的條件寫在WHERE子句的末尾，而將表

之間的連接條件置于其他WHERE子句之前，即對(duì)易排查的條件先做判斷處理，這樣在過濾掉盡可能多的記錄后再進(jìn)行等值連接，可以提高檢索效率。

例如：

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, dname

FROM emp, dept

WHERE emp.deptno = 20 AND emp.deptno = dept.deptno;

2.連接中使用表別名

在進(jìn)行連接查詢時(shí)，建議在SQL語句中使用表的別名，并把別名前綴與每個(gè)字段上。這樣可以減少解析的時(shí)間，并可避免因字段名存在歧義（使用多個(gè)

表中出現(xiàn)的同名字段）而導(dǎo)致的語法錯(cuò)誤。例如下述語句：

SELECT e.empno, e.ename, e.job, e.sal, e.deptno, d.name

FROM emp e, dept d

WHERE e.deptno = d.deptno AND e.deptno = 20;

3.用EXISTS替換DISTINCT

在進(jìn)行一對(duì)多關(guān)系的表間連接查詢時(shí)，如果要剔除結(jié)果中的重復(fù)行，可以考慮使用EXISTS（結(jié)合子查詢）替換DISTINCT。

例如：

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

4.用WHERE替換HAVING

由于SELECT語句的執(zhí)行順序?yàn)椋合龋祝龋牛遥抛泳? 在GROUP BY 子句，然后SELECT查詢，再后HAVINT子句，最后是ORDER BY子句，因此在進(jìn)行分組

查詢時(shí)，如果過濾條件不涉及分組計(jì)算，則應(yīng)該使用WHERE語句替換HAVING指定的過濾條件

例如：

SELECT deptno, avg(sal) FROM emp

WHERE deptno IN (10, 20)

GROUP BY deptno;

效率會(huì)高于下面

SELECT deptno, avg(sal) FROM emp

WHERE deptno IN (10, 20)

GROUP BY deptno;

當(dāng)然，如果分組查詢的過濾條件設(shè)計(jì)分組計(jì)算，就只能在HAVING子句中指定了。

5.子查詢優(yōu)化

1）在數(shù)據(jù)量大時(shí)，子查詢的性能會(huì)受到比較大的影響；

2）在子查詢中使用了函數(shù)to_char(add_months(to_date(tb.rptdate,'yyyymm'),12),'yyyymm')，對(duì)大數(shù)據(jù)量查詢時(shí)，執(zhí)行函數(shù)也會(huì)對(duì)系統(tǒng)產(chǎn)生比較大的影響。

下面來看一個(gè)例子：

createor replace procedure Rpt_Dyhgl_year( 
 rptyear      in varchar2, --年yyyy 
 --rpttype      in varchar2,  --報(bào)表類型，1年報(bào)，2季報(bào) 
 ref_cursor      out sys_refcursor --返回游標(biāo) 
) 
As 
 -- 變量聲明 
 。。。。。 
begin 
  delete from YWTJ_RPT_DYHGL_YEAR where RPTYEAR = rptyear and RPTTYPE = '1'; 
  commit; 
 --查詢?cè)紨?shù)據(jù) 
 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 '; 
  -- 插入新數(shù)據(jù) 
  v_sqlRptInsert := 'Insert IntoYWTJ_RPT_DYHGL_YEAR (RPTYEAR,RPTDATE,RPTTYPE) values(:RPTYEAR,:RPTDATE,:RPTTYPE)'; 
  v_sqlRptUpdate := ''; 
  -- 計(jì)算1--12月的當(dāng)月數(shù)據(jù) 
  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; 
     --計(jì)算合格率 
     。。。。。。 
     -- 更新當(dāng)月數(shù)據(jù) 
     。。。。。。 
    end loop; 
    close v_cur; 
  end loop; 
  -- 更新累計(jì)數(shù)據(jù) 
  。。。。。。 
  commit; 
  -- 計(jì)算綜合合格率 
  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;

功能運(yùn)行正常，查詢時(shí)間大約30-40秒。

然后，把報(bào)表部署到運(yùn)行環(huán)境中后，驗(yàn)證功能時(shí)，發(fā)現(xiàn)2、3分鐘報(bào)表沒有出來！多次嘗試，后來終于出來結(jié)果了：940多秒，15分鐘多！這樣用戶肯定是不能接受的！使用pl/sql dev的性能分析對(duì)Rpt_Dyhgl_year的執(zhí)行過程進(jìn)行分析，發(fā)現(xiàn)幾乎所有的時(shí)間都化在了

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

（期間把過程轉(zhuǎn)成程序執(zhí)行，性能分析時(shí)發(fā)現(xiàn)，主要時(shí)間都是由dr.Read()占用，和對(duì)過程的分析一致。）

那問題應(yīng)該是在fetch對(duì)應(yīng)的查詢上，把

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 ';      --①

單獨(dú)拿出來執(zhí)行，大約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;  --②

單獨(dú)拿出來執(zhí)行，速度很快，但返回的數(shù)據(jù)量很大。如果再對(duì)其循環(huán)分析，估計(jì)性能也好不到哪去，先行放棄這種嘗試。

把查詢②中的tongbiRuntime和tongbiBhg兩個(gè)字段子查詢?nèi)サ?，?zhí)行速度更快，同樣再把①中的tongbiRuntime和tongbiBhg兩個(gè)字段子查詢?nèi)サ?，?zhí)行速度提高很多，時(shí)間約0.157秒。

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

createor replace procedure Rpt_Dyhgl_year( 
--電壓合格率-年 
 v_rptyear      in varchar2, --年yyyy 
 --rpttype      in varchar2,  --報(bào)表類型，1年報(bào)，2季報(bào) 
 ref_cursor      out sys_refcursor --返回游標(biāo) 
) 
as 
 -- 變量聲明 
 ....... 
begin 
  delete from YWTJ_RPT_DYHGL_YEAR where RPTYEAR = v_rptyear and RPTTYPE = '1'; 
  commit; 
 --查詢?cè)紨?shù)據(jù) 
 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 '; 
  -- 插入新數(shù)據(jù) 
  v_sqlRptInsert := 'Insert IntoYWTJ_RPT_DYHGL_YEAR (RPTYEAR,RPTDATE,RPTTYPE) values(:RPTYEAR,:RPTDATE,:RPTTYPE)'; 
  v_sqlRptUpdate := ''; 
 
  -- 計(jì)算1--12月的當(dāng)月數(shù)據(jù) 
  for rptmonth in 1..12 loop 
   -- 打開游標(biāo)開始計(jì)算合格率 
   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; 
     --計(jì)算合格率 
     if v_sumRuntime > 0 then 
      v_hgl := (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime *100; 
     end if; 
     -- 更新當(dāng)月數(shù)據(jù) 
     ........ 
    end loop; 
    close v_cur; 
   -- 打開游標(biāo)開始計(jì)算同比合格率 
   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; 
     --計(jì)算合格率 
     if v_sumRuntime > 0 then 
      v_hgl := (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime*100; 
     end if; 
     -- 更新同比數(shù)據(jù) 
     ......... 
    end loop; 
    close v_cur; 
  end loop; 
 
  -- 更新累計(jì)數(shù)據(jù) 
  .........  
  -- 計(jì)算綜合合格率 
  ........ 
 
  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;

經(jīng)驗(yàn)證，執(zhí)行速度大約在一秒多，也就是說性能從原來的900多秒提高到1秒，提高差不多三個(gè)數(shù)量級(jí)。

您可能感興趣的文章:

Oracle之SQL語句性能優(yōu)化（34條優(yōu)化方法）
Oracle 查詢優(yōu)化的基本準(zhǔn)則詳解
關(guān)于Oracle多表連接,提高效率,性能優(yōu)化操作

標(biāo)簽：和田武漢承德固原周口甘肅開封青島

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Oracle數(shù)據(jù)庫中基本的查詢優(yōu)化與子查詢優(yōu)化講解》，本文關(guān)鍵詞 Oracle,數(shù)據(jù)庫,中,基本,的,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。