1. 查詢條件合理排序
Oracle采用自下而上的順序解析WHERE字據(jù)，從優(yōu)化性能角度考慮，建議將那些可以過濾掉大量記錄行的條件寫在WHERE子句的末尾，而將表
之間的連接條件置于其他WHERE子句之前，即對易排查的條件先做判斷處理，這樣在過濾掉盡可能多的記錄后再進(jìn)行等值連接，可以提高檢索效率。
例如：
|
1
2
3
4
5
|
SELECT empno, ename, job, sal, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.deptno = 20; |
要比下述語句的查詢效率高一些：
|
1
2
3
4
5
|
SELETE empno, ename, job, sal, dept.deptno, dnameFROM emp, deptWHERE emp.deptno = 20 AND emp.deptno = dept.deptno; |
2.連接中使用表別名
在進(jìn)行連接查詢時，建議在SQL語句中使用表的別名， 并把別名前綴與每個字段上。這樣可以減少解析的時間，并可避免因字段名存在歧義（使用多個
表中出現(xiàn)的同名字段）而導(dǎo)致的語法錯誤。例如下述語句：
|
1
2
3
4
5
|
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
在進(jìn)行一對多關(guān)系的表間連接查詢時，如果要剔除結(jié)果中的重復(fù)行，可以考慮使用EXISTS（結(jié)合子查詢）替換DISTINCT。
例如：
|
1
|
SELECT deptno, dname FROM dept d WHERE EXIST (SELECT 'y' FROM emp e WHERE e.deptno = d.deptno); |
4.用WHERE替換HAVING
由于SELECT語句的執(zhí)行順序為：先ＷＨＥＲＥ子句, 在GROUP BY 子句， 然后SELECT查詢， 再后HAVINT子句，最后是ORDER BY子句，因此在進(jìn)行分組
查詢時，如果過濾條件不涉及分組計算，則應(yīng)該使用WHERE語句替換HAVING指定的過濾條件
例如：
|
1
2
3
4
5
|
SELECT deptno, avg(sal) FROM empWHERE deptno IN (10, 20)GROUP BY deptno; |
效率會高于下面
|
1
2
3
4
5
|
SELECT deptno, avg(sal) FROM empWHERE deptno IN (10, 20)GROUP BY deptno; |
當(dāng)然， 如果分組查詢的過濾條件設(shè)計分組計算，就只能在HAVING子句中指定了。
5.子查詢優(yōu)化
1） 在數(shù)據(jù)量大時，子查詢的性能會受到比較大的影響；
2） 在子查詢中使用了函數(shù)to_char(add_months(to_date(tb.rptdate,'yyyymm'),12),'yyyymm')，對大數(shù)據(jù)量查詢時，執(zhí)行函數(shù)也會對系統(tǒng)產(chǎn)生比較大的影響。
下面來看一個例子：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
createor replace procedure Rpt_Dyhgl_year( rptyear in varchar2, --年yyyy --rpttype in varchar2, --報表類型，1年報，2季報 ref_cursor out sys_refcursor --返回游標(biāo) ) As -- 變量聲明 。。。。。 begin delete from YWTJ_RPT_DYHGL_YEAR where RPTYEAR = rptyear and RPTTYPE = '1'; commit; --查詢原始數(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 := ''; -- 計算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; --計算合格率 。。。。。。 -- 更新當(dāng)月數(shù)據(jù) 。。。。。。 end loop; close v_cur; end loop; -- 更新累計數(shù)據(jù) 。。。。。。 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秒。
然后，把報表部署到運行環(huán)境中后，驗證功能時，發(fā)現(xiàn)2、3分鐘報表沒有出來！多次嘗試，后來終于出來結(jié)果了：940多秒，15分鐘多！這樣用戶肯定是不能接受的！使用pl/sql dev的性能分析對Rpt_Dyhgl_year的執(zhí)行過程進(jìn)行分析，發(fā)現(xiàn)幾乎所有的時間都化在了
|
1
|
fetch v_cur into v_rptDate,v_jcdlb,v_jcdName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount,v_tongbiRuntime,v_tongbiBhg; |
（期間把過程轉(zhuǎn)成程序執(zhí)行，性能分析時發(fā)現(xiàn)，主要時間都是由dr.Read()占用，和對過程的分析一致。）
那問題應(yīng)該是在fetch對應(yīng)的查詢上，把
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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 '; --① |
單獨拿出來執(zhí)行，大約5.7秒多。
再把其中的子查詢：
|
1
2
3
4
5
6
7
8
9
10
11
|
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; --② |
單獨拿出來執(zhí)行，速度很快，但返回的數(shù)據(jù)量很大。如果再對其循環(huán)分析，估計性能也好不到哪去，先行放棄這種嘗試。
把查詢②中的tongbiRuntime和tongbiBhg兩個字段子查詢?nèi)サ簦瑘?zhí)行速度更快，同樣再把①中的tongbiRuntime和tongbiBhg兩個字段子查詢?nèi)サ簦瑘?zhí)行速度提高很多，時間約0.157秒。
這樣速度就提高了很多，但少了去年同比的數(shù)據(jù)，那就分成兩個查詢！按這個思路下來，最后得到的過程如下（只保留主要部分）：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
createor replace procedure Rpt_Dyhgl_year( --電壓合格率-年 v_rptyear in varchar2, --年yyyy --rpttype in varchar2, --報表類型，1年報，2季報 ref_cursor out sys_refcursor --返回游標(biāo) ) as -- 變量聲明 ....... begin delete from YWTJ_RPT_DYHGL_YEAR where RPTYEAR = v_rptyear and RPTTYPE = '1'; commit; --查詢原始數(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 := ''; -- 計算1--12月的當(dāng)月數(shù)據(jù) for rptmonth in 1..12 loop -- 打開游標(biāo)開始計算合格率 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; -- 更新當(dāng)月數(shù)據(jù) ........ end loop; close v_cur; -- 打開游標(biāo)開始計算同比合格率 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; -- 更新同比數(shù)據(jù) ......... end loop; close v_cur; end loop; -- 更新累計數(shù)據(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)驗證，執(zhí)行速度大約在一秒多，也就是說性能從原來的900多秒提高到1秒，提高差不多三個數(shù)量級。
