ORACLE中SQL查詢范文
本站小編為你精心準(zhǔn)備了ORACLE中SQL查詢參考范文，愿這些范文能點(diǎn)燃您思維的火花，激發(fā)您的寫作靈感。歡迎深入閱讀并收藏。
摘要數(shù)據(jù)庫性能問題一直是決策者及技術(shù)人員共同關(guān)注的焦點(diǎn)，影響數(shù)據(jù)庫性能的一個(gè)重要因素就是SQL查詢語句的低效率。論文首先分析了導(dǎo)致SQL查詢語句性能低下的四個(gè)常見原因以及SQL調(diào)優(yōu)的一般步驟，然后分別針對(duì)如何降低I/O操作、在查詢語句中如何避免對(duì)查詢結(jié)果的高成本操作以及在多表連接時(shí)如何提高查詢效率進(jìn)行了分析。
關(guān)鍵詞oracle；sql；優(yōu)化；連接
1引言
隨著網(wǎng)絡(luò)應(yīng)用不斷發(fā)展，系統(tǒng)性能已越來越引起決策者的重視。影響系統(tǒng)性能的因素很多，低效的SQL語句就是其中一個(gè)不可忽視的重要原因。論文首先分析導(dǎo)致SQL性能低下的常見原因，然后分析SQL調(diào)優(yōu)應(yīng)遵循的一般步驟，最后從如何降低I/O、避免對(duì)查詢結(jié)果的高成本操作和多表連接中如何提高SQL性能進(jìn)行了研究。鑒于目前ORACLE在數(shù)據(jù)庫市場(chǎng)上的主導(dǎo)地位，論文將只針對(duì)ORACLE進(jìn)行討論。
2影響SQL性能的原因
影響SQL性能的因素很多，如初始化參數(shù)設(shè)置不合理、導(dǎo)入了不準(zhǔn)確的系統(tǒng)及模式統(tǒng)計(jì)數(shù)據(jù)從而影響優(yōu)化程序(CBO)的正確判斷等，這些往往和DBA密切相關(guān)。純粹從SQL語句出發(fā)，筆者認(rèn)為影響SQL性能不外乎以下四個(gè)重要原因：
(1)在大記錄集上進(jìn)行高成本操作，如使用了引起排序的謂詞等。
(2)過多的I/O操作(含物理I/O與邏輯I/O)，最典型的就是未建立恰當(dāng)?shù)乃饕瑢?dǎo)致對(duì)查詢表進(jìn)行全表掃描。
(3)處理了太多的無用記錄，如在多表連接時(shí)過濾條件位置不當(dāng)導(dǎo)致中間結(jié)果集包含了太多的無用記錄。
(4)未充分利用數(shù)據(jù)庫提供的功能，如查詢的并行化處理等。
第(4)個(gè)原因處理起來相對(duì)簡(jiǎn)單。論文將針對(duì)前三個(gè)原因論述如何提高SQL查詢語句的性能。
3SQL優(yōu)化的一般步驟
SQL優(yōu)化一般需經(jīng)過發(fā)現(xiàn)問題、分析問題、提出解決措施、應(yīng)用措施、測(cè)試性能幾個(gè)步驟，如圖1所示。“發(fā)現(xiàn)問題就是解決問題的一半”，因此在SQL調(diào)優(yōu)過程中，定位問題SQL是非常重要的一步，一般可借助于ORACLE自帶的性能優(yōu)化工具如STATSPACK、TKPROF、AUTOTRACE等輔助用戶進(jìn)行，同時(shí)還應(yīng)該重視動(dòng)態(tài)性能視圖如V$SQL、V$MYSTAT、V$SYSSTAT等的研究。
圖1SQL優(yōu)化的一般步驟
4SQL語句的優(yōu)化4.1優(yōu)化排序操作
排序的成本十分高昂，當(dāng)在查詢語句中使用了引起結(jié)果集排序的謂詞時(shí)，SQL性能必然受到影響。
4.1.1排序過程分析
當(dāng)待排序數(shù)據(jù)集不是太大時(shí)，服務(wù)器在內(nèi)存(排序區(qū))完成排序操作，如果排序需要更多的內(nèi)存空間，服務(wù)器將進(jìn)行如下處理：
(1)將數(shù)據(jù)分成多個(gè)小的集合，對(duì)每一集合進(jìn)行排序。
(2)服務(wù)器向磁盤申請(qǐng)臨時(shí)空間，將排好序的中間結(jié)果寫入臨時(shí)段，再對(duì)另外的集合進(jìn)行排序。
(3)在所有的集合均排好序后，服務(wù)器再將它們進(jìn)行合并得到最終的結(jié)果，如果排序區(qū)尺寸太小，合并無法一次完成時(shí)，將分多次進(jìn)行。
從上述分析可知，排序是一種十分昂貴的操作，它消耗大量的CPU時(shí)間和內(nèi)存，觸發(fā)磁盤分頁和交換操作，因此只要有可能，我們就應(yīng)該在SQL語句中盡量避免排序操作。
4.1.2SQL中引起排序的操作
SQL查詢語句中引起排序的操作大致有：ORDERBY和GROUPBY從句；DISTINCT修飾符；UNION、INTERSECT、MINUS集合操作符；多表連接時(shí)的排序合并連接(SORTMERGEJOIN)等。
4.1.3如何避免排序
1)建立恰當(dāng)?shù)乃饕?/p>
對(duì)經(jīng)常進(jìn)行排序和連接操作的字段建立索引。在建立索引后，當(dāng)服務(wù)器向這些字段發(fā)出排序請(qǐng)求時(shí)，將直接引用索引而不進(jìn)行排序操作；當(dāng)進(jìn)行等值連接查詢操作時(shí)，若建立連接的字段未建立索引，服務(wù)器進(jìn)行的是排序合并連接(SORTMERGEJOIN)，連接操作的過程如下：
對(duì)進(jìn)行連接的兩個(gè)或多個(gè)表分別進(jìn)行全掃描；
對(duì)每一個(gè)表中的行集分別進(jìn)行全排序；
合并排序結(jié)果。
如果建立連接的字段已建立索引，服務(wù)器進(jìn)行嵌套循環(huán)連接(NESTEDLOOPJOINS)，該連接方式不需要任何排序，其過程如下：
對(duì)驅(qū)動(dòng)表進(jìn)行全表掃描；
對(duì)返回的每一行利用連接字段值實(shí)施索引惟一掃描；
利用從索引掃描中返回的ROWID值在從表中定位記錄；
合并主、從表中的匹配記錄。
因此，建立索引可避免多數(shù)排序操作。
2)用UNIIONALL替換UNION
UNION在進(jìn)行表鏈接后會(huì)篩選掉重復(fù)的記錄，所以在表鏈接后會(huì)對(duì)所產(chǎn)生的結(jié)果集進(jìn)行排序運(yùn)算，刪除重復(fù)的記錄再返回結(jié)果。大部分應(yīng)用中是不會(huì)產(chǎn)生重復(fù)記錄的，最常見的是過程表與歷史表UNION。因此，采用UNIONALL操作符替代UNION，因?yàn)閁NIONALL操作只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回。
4.2優(yōu)化I/O
過多的I/O操作會(huì)占用CPU時(shí)間、消耗大量?jī)?nèi)存和占用過多的栓鎖，因此有必要對(duì)SQL的I/O進(jìn)行優(yōu)化。優(yōu)化I/O的最有效方式就是用索引掃描代替全表掃描。
4.2.1應(yīng)用基于函數(shù)的索引
基于函數(shù)的索引(FUNCTIONBASEDINDEX，簡(jiǎn)記為FBI)提供了索引計(jì)算列并在查詢中使用這些索引的能力。FBI的實(shí)質(zhì)是對(duì)查詢所需中間結(jié)果進(jìn)行預(yù)處理。如果一個(gè)FBI與查詢語句中的內(nèi)嵌函數(shù)完全匹配，CBO在生成查詢計(jì)劃時(shí)，將自動(dòng)啟用索引范圍掃描(INDEXRANGESCAN)替換全表掃描(FULLbrSCAN)。考察下面的代碼段并用AUTOTRACE觀察創(chuàng)建FBI前后執(zhí)行計(jì)劃的變化。
select*fromempwhereupper(ename)=’SCOTT’
創(chuàng)建FBI前，很明顯是全表掃描。
ExecutionPlan
……
10brACCESS(FULL)OF''''EMPLOYEES''''(Cost=2Card=1Bytes=22)
idle>CREATEINDEXEMP_UPPER_FIRST_NAMEONEMPLOYEES(UPPER(FIRST_NAME))；
索引已創(chuàng)建。
再次運(yùn)行相同查詢，
ExecutionPlan
……
10brACCESS(BYINDEXROWID)OF''''EMPLOYEES''''(Cost=1Card=1Bytes=22)
21INDEX(RANGESCAN)OF''''EMP_UPPER_FIRST_NAME''''(NON-UNIQUE)(Cost=1Card=1)
這一簡(jiǎn)單的例子充分說明了FBI在SQL查詢優(yōu)化中的作用。FBI所用的函數(shù)可以是用戶自己創(chuàng)建的函數(shù)，該函數(shù)越復(fù)雜，基于該函數(shù)創(chuàng)建FBI對(duì)SQL查詢性能的優(yōu)化作用越明顯。
4.2.2應(yīng)用物化視圖和查詢重寫
物化視圖是一個(gè)預(yù)計(jì)算結(jié)果集，其中通常包含聚集與多表連接等復(fù)雜操作。數(shù)據(jù)庫自動(dòng)維護(hù)物化視圖，且隨用戶的要求進(jìn)行刷新。查詢重寫機(jī)制就是用數(shù)據(jù)庫中的替代對(duì)象(如物化視圖)將用戶提交的查詢重寫為完全不同但功能等價(jià)的查詢。查詢重寫對(duì)用戶透明，用戶完全按常規(guī)編寫訪問數(shù)據(jù)庫的查詢語句，優(yōu)化程序(CBO)自動(dòng)決定是否對(duì)用戶提交的查詢進(jìn)行重寫。查詢重寫是提高查詢性能的一種非常有效的方法，尤其是在數(shù)據(jù)倉庫環(huán)境中針對(duì)匯總、多表連接以及其它高成本的操作方面。
下面以一個(gè)非常簡(jiǎn)單的例子來演示物化視圖和查詢重寫在優(yōu)化SQL查詢性能方面的作用。
selectdept.deptno，dept.dname，count(*)
fromemp，dept
whereemp.deptno=dept.deptno
groupbydept.deptno，dept.dname
查詢計(jì)劃及主要統(tǒng)計(jì)數(shù)據(jù)如下：
執(zhí)行計(jì)劃：
-----------------------------------------
……
21HASHJOIN(Cost=5Card=14Bytes=224)
32brACCESS(FULL)OF''''DEPT''''(Cost=2Card=4Bytes=52)
42brACCESS(FULL)OF''''EMP''''(Cost=2Card=14Bytes=42)
主要統(tǒng)計(jì)數(shù)據(jù)：
-----------------------------------------
305recursivecalls
46consistentgets
創(chuàng)建物化視圖EMP_DEPT：
creatematerializedviewemp_deptbuildimmediate
refreshondemand
enablequeryrewrite
as
selectdept.deptno，dept.dname，count(*)
fromemp，dept
whereemp.deptno=dept.deptno
groupbydept.deptno，dept.dname
/
再次執(zhí)行查詢，執(zhí)行計(jì)劃及主要統(tǒng)計(jì)數(shù)據(jù)如下：
執(zhí)行計(jì)劃：
-------------------------------------
……
10brACCESS(FULL)OF''''EMP_DEPT''''(Cost=2Card=327Bytes=11445)
主要統(tǒng)計(jì)數(shù)據(jù)：
------------------------------------
79recursivecalls
28consistentgets
可見，在建立物化視圖之前，首先執(zhí)行兩個(gè)表的全表掃描，然后進(jìn)行HASH連接，再進(jìn)行分組排序和選擇操作；而建立物化視圖后，CBO自動(dòng)將上述復(fù)雜操作轉(zhuǎn)換為對(duì)物化視圖EMP_DEPT的全掃描，相關(guān)的統(tǒng)計(jì)數(shù)據(jù)也有了很大的改善，遞歸調(diào)用(RECURSIVECALLS)由305降到79，邏輯I/O(CONSISTENTGETS)由46降為28。
4.2.3將頻繁訪問的小表讀入CACHE
邏輯I/O總是快于物理I/O。如果數(shù)據(jù)庫中存在被應(yīng)用程序頻繁訪問的小表，可將這些表強(qiáng)行讀入KEEP池，從而避免物理I/O的發(fā)生。
4.3多表連接優(yōu)化
最能體現(xiàn)查詢復(fù)雜性的就是多表連接，多表連接操作往往要耗費(fèi)大量的CPU時(shí)間和內(nèi)存，因此多表連接查詢性能優(yōu)化往往是SQL優(yōu)化的重點(diǎn)與難點(diǎn)。
4.3.1消除外部連接
通過消除外部連接，不僅使得到的查詢更易于讀取，而且性能也經(jīng)常可以得到改善。一般的思路是，有以下形式的查詢：
SELECT…，OUTER_JOINED_br.COLUMN
FROMSOME_br，OUTER_JOINED_TO_br
WHERE…=OUTER_JOINED_TO_br(+)
可轉(zhuǎn)換為如下形式的查詢：
SELECT…，(SELECTCOLUMNFROMOUTER_JOINED_TO_brWHERE…)FROMSOME_br；
4.3.2謂詞前推，優(yōu)化中間結(jié)果
多表連接的性能低下多數(shù)是因?yàn)檫B接操作與過濾操作的次序不合理，大多數(shù)用戶在編寫多表連接查詢時(shí)，總是先進(jìn)行連接操作再應(yīng)用過濾條件，這導(dǎo)致服務(wù)器做了太多的無用功。針對(duì)這類問題，其優(yōu)化思路就是盡可能將過濾謂詞前推，使不符合條件的記錄提前被篩選掉，只對(duì)符合條件的少數(shù)記錄進(jìn)行連接處理，這樣可成倍的提高SQL查詢效能。
如下圖所示的星形模型，現(xiàn)要統(tǒng)計(jì)最近三個(gè)月進(jìn)貨的商品在各種銷售渠道上的銷售業(yè)績(jī)。
圖2產(chǎn)品銷售的星形模型
標(biāo)準(zhǔn)連接查詢?nèi)缦拢?/p>
Selecta.prod_name，sum(b.sale_quant)，
sum(c.sale_quant)，sum(d.sale_quant)
Fromproducta，tele_saleb，online_salec，store_saled
Wherea.prod_id=b.prod_idanda.prod_id=c.prod_id
anda.prod_id=d.prod_idAnda.order_date>sysdate-90
Groupbya.prod_id；
啟用內(nèi)嵌視圖，且將條件a.order_date>sysdate-90前移，優(yōu)化后代碼如下：
Selecta.prod_name，b.tele_sale_sum，c.online_sale_sum，d.store_sale_sumFromproducta，
(selectsum(sal_quant)tele_sale_sumfromproduct，tele_sale
Whereproduct.order_date>sysdate-90andproduct.prod_id=tele_sale.prod_id)b，
(selectsum(sal_quant)online_sale_sum
fromproduct，tele_sale
Whereproduct.order_date>sysdate-90andproduct.prod_id=online_sale.prod_id)c，
(selectsum(sal_quant)store_sale_sum
fromproduct，store_sale
Whereproduct.order_date>sysdate-90andproduct.prod_id=store_sale.prod_id)d，
Wherea.prod_id=b.prod_idand
a.prod_id=c.prod_idanda.prod_id=d.prod_id；
5結(jié)束語
SQL語言在數(shù)據(jù)庫應(yīng)用中占有非常重要的地位，其性能的優(yōu)劣直接影響著整個(gè)信息系統(tǒng)的可用性。論文從影響SQL性能的最主要的三個(gè)方面入手，分析了如何優(yōu)化SQL查詢的I/O、避免高成本的排序操作和優(yōu)化多表連接。需要強(qiáng)調(diào)的一點(diǎn)是，理解SQL語句所解決的問題比SQL調(diào)優(yōu)本身更重要，因此SQL調(diào)優(yōu)需要系統(tǒng)分析人員、開發(fā)人員和數(shù)據(jù)庫管理員密切協(xié)作。
參考文獻(xiàn)
[1]ThomasKyte.EffectiveOraclebyDesign：DesignandBuildHigh-performanceOracleApplication[M]，TheMcGral-HillCompanies，Inc，2003
[2]KevinLoney，GeorgeKoch，Oracle9i：TheCompleteReference[M]，TheMcGral-HillCompanies，Inc，2002
[3]Oracle9iSQLReferencerelease2(9.2)[OL/M]，2002.10.http：///technology/
[4]Oracle9iDataWarehousingGuiderelease2(9.2)[OL/M]，2002.03.http：///technology/
[5]AlexeyDanchenkov，DonaldBurleson，OracleTuning：TheDefinitiveReference[OL/M]，RampantTechpress，2006.
[6]Oracle9iDatabaseConceptsrelease2(9.2)[OL/M]，2002.08.http：///technology/
[7]Oracle9isuppliedplsqlpackagesandtypesreferencerelease2(9.2)[OL/M]，2002.12.http：///technology/