Oracle數(shù)據(jù)庫(kù)中基本的查詢優(yōu)化與子查詢優(yōu)化講解
1. 查詢條件合理排序
Oracle采用自下而上的順序解析WHERE字據(jù)，從優(yōu)化性能角度考慮，建議將那些可以過(guò)濾掉大量記錄行的條件寫(xiě)在WHERE子句的末尾，而將表
之間的連接條件置于其他WHERE子句之前，即對(duì)易排查的條件先做判斷處理，這樣在過(guò)濾掉盡可能多的記錄后再進(jìn)行等值連接，可以提高檢索效率。
例如：
SELECT empno, ename, job, sal, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.deptno = 20;
要比下述語(yǔ)句的查詢效率高一些：
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語(yǔ)句中使用表的別名， 并把別名前綴與每個(gè)字段上。這樣可以減少解析的時(shí)間，并可避免因字段名存在歧義（使用多個(gè)
表中出現(xiàn)的同名字段）而導(dǎo)致的語(yǔ)法錯(cuò)誤。例如下述語(yǔ)句：
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語(yǔ)句的執(zhí)行順序?yàn)椋合龋祝龋牛遥抛泳? 在GROUP BY 子句， 然后SELECT查詢， 再后HAVINT子句，最后是ORDER BY子句，因此在進(jìn)行分組
查詢時(shí)，如果過(guò)濾條件不涉及分組計(jì)算，則應(yīng)該使用WHERE語(yǔ)句替換HAVING指定的過(guò)濾條件
例如：
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)然， 如果分組查詢的過(guò)濾條件設(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)生比較大的影響。
下面來(lái)看一個(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)表沒(méi)有出來(lái)！多次嘗試，后來(lái)終于出來(lái)結(jié)果了：940多秒，15分鐘多！這樣用戶肯定是不能接受的！使用pl/sql dev的性能分析對(duì)Rpt_Dyhgl_year的執(zhí)行過(guò)程進(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;
（期間把過(guò)程轉(zhuǎn)成程序執(zhí)行，性能分析時(shí)發(fā)現(xiàn)，主要時(shí)間都是由dr.Read()占用，和對(duì)過(guò)程的分析一致。）
那問(wèn)題應(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ú)拿出來(lái)執(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ú)拿出來(lái)執(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è)思路下來(lái)，最后得到的過(guò)程如下（只保留主要部分）：
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
-- 打開(kāi)游標(biāo)開(kāi)始計(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;
-- 打開(kāi)游標(biāo)開(kāi)始計(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í)行速度大約在一秒多，也就是說(shuō)性能從原來(lái)的900多秒提高到1秒，提高差不多三個(gè)數(shù)量級(jí)。
相關(guān)文章
Oracle過(guò)程與函數(shù)的區(qū)別分析
在Oracle數(shù)據(jù)庫(kù)中，過(guò)程和函數(shù)都以編譯后的形式存放在數(shù)據(jù)庫(kù)中，二者的主要區(qū)別在于他們的調(diào)用方式，下文對(duì)二者的區(qū)別作了詳盡的描述，供您參考2014-08-08
Oracle數(shù)據(jù)庫(kù)的系統(tǒng)結(jié)構(gòu)
這篇文章主要介紹了Oracle數(shù)據(jù)庫(kù)的系統(tǒng)結(jié)構(gòu),Oracle數(shù)據(jù)庫(kù)的存儲(chǔ)結(jié)構(gòu)(Storage Structure)分為物理存儲(chǔ)結(jié)構(gòu)和邏輯存儲(chǔ)結(jié)構(gòu)兩種,分別描述了在操作系統(tǒng)中和數(shù)據(jù)庫(kù)系統(tǒng)內(nèi)部數(shù)據(jù)的組織管理方式,需要的朋友可以參考下2023-07-07
Oracle試用到期如何刪除注冊(cè)表繼續(xù)試用30天
這篇文章主要介紹了Oracle試用到期如何刪除注冊(cè)表繼續(xù)試用30天，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-04-04
Oracle數(shù)據(jù)庫(kù)創(chuàng)建用戶與數(shù)據(jù)庫(kù)備份小結(jié)(必看篇)
下面小編就為大家推薦一篇Oracle數(shù)據(jù)庫(kù)創(chuàng)建用戶與數(shù)據(jù)庫(kù)備份小結(jié)。具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2018-02-02
我們?cè)谌粘９ぷ髦胁榭幢砜臻g的使用情況大多數(shù)都是使用DBA。下面腳本之家小編給大家?guī)?lái)了oracle表空間查看sql使用情況的方法步驟，需要的童鞋參考下2016-05-05
oracle數(shù)據(jù)庫(kù)截取字符串substr函數(shù)使用舉例
在Oracle數(shù)據(jù)庫(kù)中可以使用SUBSTR函數(shù)來(lái)截取字符串,這篇文章主要給大家介紹了關(guān)于oracle數(shù)據(jù)庫(kù)截取字符串substr函數(shù)使用的相關(guān)資料,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-01-01
這篇文章主要介紹了Oracle修改表空間大小的方法,實(shí)例講述了Oracle通過(guò)命令行實(shí)現(xiàn)修改Oracle表空間大小的方法,非常簡(jiǎn)單實(shí)用,需要的朋友可以參考下2015-12-12
今天遇到個(gè)軟件要求安裝oracle client端，于是考慮裝精簡(jiǎn)版本的,以下是具體的安裝步驟，特分享下，方便需要的朋友2014-06-06
Oracle?exadata存儲(chǔ)節(jié)點(diǎn)更換內(nèi)存操作及報(bào)錯(cuò)處理方法
在進(jìn)行Oracle?Exadata巡檢時(shí),發(fā)現(xiàn)cell節(jié)點(diǎn)內(nèi)存報(bào)錯(cuò),需確認(rèn)內(nèi)存PN號(hào)及大小,并更換備件,這篇文章主要介紹了Oracle?exadata存儲(chǔ)節(jié)點(diǎn)更換內(nèi)存操作及報(bào)錯(cuò)處理的相關(guān)資料,需要的朋友可以參考下2024-10-10
這篇文章主要介紹了oracle橫向縱向求和的相關(guān)內(nèi)容，涉及兩個(gè)實(shí)例，具有一定參考價(jià)值。需要的朋友可以了解。2017-10-10