Oracle數(shù)據(jù)庫(kù)查詢語(yǔ)句的優(yōu)化研究

吳健

【摘 要】相比較一般數(shù)據(jù)庫(kù)，Oracle的性能更加優(yōu)秀，存儲(chǔ)的級(jí)別更大，使用的范圍更加廣泛，因而對(duì)Oracle數(shù)據(jù)庫(kù)的研究才更顯得有必要。對(duì)于Oracle數(shù)據(jù)庫(kù)性能的研究中， 查詢語(yǔ)句的執(zhí)行效率十分重要， 特別對(duì)于大型數(shù)據(jù)庫(kù)系統(tǒng)， SQL查詢語(yǔ)句效率的高低相比可能有上百倍之差。下文將重點(diǎn)介紹了Oracle數(shù)據(jù)庫(kù)SQL查詢語(yǔ)句的處理過(guò)程， 再對(duì)不同優(yōu)化器工作原理進(jìn)行了解析， 分析若干方法去提升Oracle數(shù)據(jù)庫(kù)查詢優(yōu)化性能， 同時(shí)也能使我們進(jìn)一步去了解Oracle數(shù)據(jù)庫(kù)，方便我們的工作生活。

【關(guān)鍵詞】Oracle數(shù)據(jù)庫(kù)；SQL語(yǔ)句；優(yōu)化；效率

0 導(dǎo)言

數(shù)據(jù)庫(kù)技術(shù)是計(jì)算機(jī)科學(xué)技術(shù)發(fā)展最快、應(yīng)用最為廣泛的信息技術(shù)之一，數(shù)據(jù)庫(kù)技術(shù)已經(jīng)從相對(duì)傳統(tǒng)的商業(yè)領(lǐng)域不斷擴(kuò)大到許多新的領(lǐng)域，如醫(yī)療、多媒體、經(jīng)濟(jì)、政治、科研等領(lǐng)域。其中Oracle數(shù)據(jù)庫(kù)是應(yīng)用最為廣泛的數(shù)據(jù)庫(kù)，其中在通信領(lǐng)域范圍內(nèi)，全球20家排名頂尖的通信公司都是使用Oracle產(chǎn)品；在IT服務(wù)公司中，中國(guó)前100大的IT公司（HP、IBM、凱建、埃森哲、東軟等）全部都用Oracle相關(guān)技術(shù)。對(duì)Oracle數(shù)據(jù)庫(kù)的優(yōu)化研究就是進(jìn)行有目的性的優(yōu)化其組件以改善Oracle數(shù)據(jù)庫(kù)的性能，即增加數(shù)據(jù)庫(kù)的吞吐量和減少數(shù)據(jù)庫(kù)的響應(yīng)時(shí)間，因而從大多數(shù)Oracle系統(tǒng)的應(yīng)用實(shí)例來(lái)觀察，其中數(shù)據(jù)庫(kù)的查詢操作在各種操作中所占的比率是最大的，查詢效率成為了影響Oracle數(shù)據(jù)庫(kù)各項(xiàng)性能的關(guān)鍵因素。所以本文將介紹Oracle數(shù)據(jù)庫(kù)查詢語(yǔ)句的一些優(yōu)化研究方案。

1 Oracle數(shù)據(jù)庫(kù)SQL語(yǔ)句處理過(guò)程

一般來(lái)說(shuō)，數(shù)據(jù)庫(kù)處理SQL語(yǔ)句都要經(jīng)過(guò)三個(gè)過(guò)程：分析、執(zhí)行、返回結(jié)果，比如Congnos ReportNet通過(guò)拖放完成表現(xiàn)層后，能夠自動(dòng)地生成SQL語(yǔ)句，然后將生成的SQL語(yǔ)句傳遞到Oracle數(shù)據(jù)庫(kù)中進(jìn)行數(shù)據(jù)處理。

1.1 分析

分析是處理SQL語(yǔ)句的第一步，它包含了以下幾個(gè)方面：

（1）語(yǔ)法分析：Oracle是采用數(shù)據(jù)庫(kù)常用的自下向上的分析方法，包含檢驗(yàn)語(yǔ)法規(guī)范，命名規(guī)范，它是處理SQL語(yǔ)句中消耗時(shí)間做多且代價(jià)最高的步驟；增加Oracle數(shù)據(jù)庫(kù)的查詢效率可以主要表現(xiàn)在綁定變量和存儲(chǔ)過(guò)程兩個(gè)方面：

A.綁定變量：如果使用SQL語(yǔ)句查詢的頻率越高，系統(tǒng)硬件消耗的資源也將會(huì)變大，以至于了減少系統(tǒng)的資源使用空間，導(dǎo)致用戶的訪問(wèn)數(shù)量降低，另外同時(shí)其它查詢語(yǔ)句也將被從共享池中踢出， 而使用綁定變量，提交給相同對(duì)象的同樣的查詢的用戶唯一使用就能夠被重復(fù)使用，其效率能大大地增強(qiáng)。

B.存儲(chǔ)過(guò)程：存儲(chǔ)過(guò)程只會(huì)是在創(chuàng)建時(shí)編譯，然后的每次執(zhí)行都再也不需要進(jìn)行編譯，而普通的SQL語(yǔ)句執(zhí)行一次都需要進(jìn)行編譯執(zhí)行，相比較，盡量使用存儲(chǔ)過(guò)程就會(huì)增強(qiáng)Oracle數(shù)據(jù)庫(kù)的執(zhí)行效率。

（2）語(yǔ)義分析，學(xué)過(guò)編譯原理的應(yīng)該會(huì)知道這一步是相當(dāng)重要的，Oracle主要是分析SQL語(yǔ)句格式正確與否，各個(gè)對(duì)象是否存在，以及它是否擁有足夠的權(quán)限執(zhí)行。

SQL>SELECT DEPT_NO，DEPT_NAME，DEPT_LOC FROM DEPT；

提示：ERROR at line 1：

ORA-00942： table or view does not exist

由于查詢沒(méi)有可供訪問(wèn)的DEPT（部門表）對(duì)象，因此該SQL語(yǔ)句無(wú)法通過(guò)語(yǔ)義檢測(cè)，不能查詢結(jié)果。

（3）表達(dá)式轉(zhuǎn)換，將復(fù)雜的SQL表達(dá)式轉(zhuǎn)換成為相對(duì)應(yīng)的對(duì)于基表的SQL查詢語(yǔ)句，寫出SQL的標(biāo)準(zhǔn)。

A.不要在WHERE中進(jìn)行表達(dá)式計(jì)算，例如SELECT * FROM EMP WHERE DEPT_NO>=10*10 AND DEPT_NO >（10+1）*10是不被允許的查詢語(yǔ)句。

B.數(shù)據(jù)類型的匹配

SELECT EMP_SALARY FROM EMP WHERE EMP_SALARY>100 沒(méi)有SELECT EMP_SALARY FROM EMP WHERE EMP_SALARY>100.00好了。

（4）選擇優(yōu)化器。Oracle的優(yōu)化器共有3種：

1）RULE （基于規(guī)則 2）COST （基于成本 3）CHOOSE （選擇性）

在默認(rèn)的情況下，Oracle數(shù)據(jù)庫(kù)一般選用的是選擇性優(yōu)化器， 目的就是規(guī)避不需要的全表掃描，但是實(shí)際應(yīng)用中還是盡量不使用選擇性優(yōu)化器，而采用的是基于規(guī)則或者基于成本的優(yōu)化器。當(dāng)然，最好是能根據(jù)批量的SQL自己編制一個(gè)特定的優(yōu)化器來(lái)幫助自己優(yōu)化SQL查詢。

（5）選擇連接方式。優(yōu)化器使用了六種不同的連接方式（不做詳解）：

1 嵌套循環(huán)連接（NESTED LOOP JOIN）

2 群集連接 （CLUSTER JOIN）

3 排序連接（SORT MERGE JOIN）

4 笛卡爾連接（CARTESIAN JOIN）

5 哈希連接（HASH JOIN）

6 索引連接（INDEX JOIN）

這六種連接方式都存在各自獨(dú)特的技術(shù)特性，在特定的條件下，各自高效的性能都能夠得到充分的發(fā)揮。

（6）選擇連接順序。在Oracle SQL查詢語(yǔ)句中最好的方法是手工指定表的連接順序。為了盡快創(chuàng)建最小的解決方案集，這里所遵循的規(guī)則是將表結(jié)合起來(lái)，通常優(yōu)先使用限制最嚴(yán)格的WHERE子句來(lái)連接表。

選擇數(shù)據(jù)搜索路徑。根據(jù)以上條件選擇合適的數(shù)據(jù)搜索路徑，能使SQL語(yǔ)句執(zhí)行的更加有效率。

1.2 執(zhí)行

主要在于使用更新和刪除的SQL語(yǔ)句時(shí)，必須要鎖定數(shù)據(jù)列，防止其他用戶進(jìn)行修改。Oracle會(huì)先在數(shù)據(jù)庫(kù)緩沖中尋找是否存在所需要的數(shù)據(jù)塊，如果存在這樣的數(shù)據(jù)塊，就可以直接進(jìn)行讀或者修改操作，否則數(shù)據(jù)會(huì)被從物理文件之中讀取到數(shù)據(jù)庫(kù)緩沖區(qū)中。

1.3 返回結(jié)果

對(duì)SELECT 語(yǔ)句需要返回結(jié)果的語(yǔ)句，看返回的結(jié)果集是否需要按照實(shí)際應(yīng)用中排序，若需要，則排序后輸出。

2 Oracle優(yōu)化器工作原理

Oracle優(yōu)化器分為三類：

1）RULE優(yōu)化器：基于規(guī)則的優(yōu)化器相對(duì)而言比較簡(jiǎn)單，工作原理概括為檢查數(shù)據(jù)庫(kù)當(dāng)中的所有的可用路徑并且將這些可用的路徑與已經(jīng)存在的路徑表進(jìn)行比較，來(lái)確定SQL語(yǔ)句具體的執(zhí)行過(guò)程。RULE優(yōu)化的過(guò)程中不需要任何表或索引的統(tǒng)計(jì)信息，而且也會(huì)忽略任何表或索引的統(tǒng)計(jì)信息 。

2）COST優(yōu)化器：基于開銷的優(yōu)化器相對(duì)比較復(fù)雜，通過(guò)使用數(shù)據(jù)庫(kù)的結(jié)構(gòu)和數(shù)據(jù)等信息來(lái)選擇最優(yōu)的執(zhí)行計(jì)劃。優(yōu)化的過(guò)程中需要相關(guān)表和索引的統(tǒng)計(jì)信息。

3）CHOOSE優(yōu)化器：在存在相關(guān)統(tǒng)計(jì)信息的情況下采用基于開銷的優(yōu)化器，在不存在相關(guān)統(tǒng)計(jì)信息的情況下才用基于規(guī)則的優(yōu)化器。這是8I中默認(rèn)的優(yōu)化器工作方式。

同樣的查詢語(yǔ)句可以有很多不同的執(zhí)行計(jì)劃， Cost優(yōu)化器將自動(dòng)計(jì)算不同執(zhí)行計(jì)劃所消耗的系統(tǒng)資源， 接著就會(huì)選出消耗最低的執(zhí)行計(jì)劃。一條查詢的消耗大致被分為3個(gè)基本成分：I/O消耗、CPU消耗、Network消耗。

3 Oracle查詢優(yōu)化方法

3.1 共享SQL語(yǔ)句

Oracle在執(zhí)行每條SQL語(yǔ)句的時(shí)候都會(huì)先對(duì)語(yǔ)句進(jìn)行語(yǔ)法分析，而這個(gè)過(guò)程是比較消耗資源的，為了能夠省略這個(gè)步驟來(lái)提高SQL查詢語(yǔ)句的執(zhí)行效率，Oracle采用的共享SQL語(yǔ)句的方式，就是把每條從用戶發(fā)出的SQL存儲(chǔ)到系統(tǒng)全局區(qū)（System Global Area）的共享池當(dāng)中，這個(gè)共享池當(dāng)中的所有SQL語(yǔ)句將會(huì)被數(shù)據(jù)庫(kù)當(dāng)中的所有有權(quán)限的用戶共享使用；所以當(dāng)你每天執(zhí)行一條SQL語(yǔ)句時(shí)，Oracle會(huì)先去共享池當(dāng)中查找是否存在這樣一條SQL語(yǔ)句的執(zhí)行記錄，如果存在則會(huì)直接得到該SQL語(yǔ)句的執(zhí)行計(jì)劃和執(zhí)行路徑，這么就大大的節(jié)省了系統(tǒng)內(nèi)存并且提高了SQL語(yǔ)句的執(zhí)行效率。

如果某一查詢語(yǔ)句需要匹配共享池之中的SQL， 都必須遵照以下三條規(guī)則。

（1）SQL語(yǔ)句之間字符之間必須滿足相互匹配，另外字符的大小寫也需要相互匹配。

（2）SQL語(yǔ)句中的對(duì)象必須完全一致，對(duì)于如下查詢：

SELECT MAX（EMP_SALARY） FROM EMP LIMIT；

（3）SQL語(yǔ)句中使用命名的綁定變量必須相同。

3.2 創(chuàng)建和使用索引

在關(guān)系型數(shù)據(jù)庫(kù)中，索引是數(shù)據(jù)庫(kù)之中的一種結(jié)構(gòu)。簡(jiǎn)而言之，索引的作用類似于于圖書的目錄，使用索引相當(dāng)于使用這個(gè)目錄快速找到所需文章的內(nèi)容，它能夠使作用于表的查詢語(yǔ)句執(zhí)行得更加有效率。對(duì)于一個(gè)數(shù)據(jù)庫(kù)而言，索引是必須的，但是對(duì)于現(xiàn)在存在的各種大型數(shù)據(jù)庫(kù)而言，索引強(qiáng)大的作用大大提升數(shù)據(jù)庫(kù)的查詢性能，使其變成了現(xiàn)在數(shù)據(jù)庫(kù)不可或缺的重要部分。索引的創(chuàng)建需要去維護(hù)， 如果對(duì)表進(jìn)行更新、插入、刪去等操作的時(shí)候， 已經(jīng)創(chuàng)建好的索引也需要去更新。因而在使用索引時(shí)需要遵循以下的原則：

（1）對(duì)于查詢操作時(shí)較少需要的列或者列的重復(fù)值比較多，則不需要建立索引。

（2）對(duì)于按范圍查詢的列，最好建立索引。

（3）如果一個(gè)表中包含了主鍵和外鍵，則必須要建立索引。

（4）對(duì)于一些特殊的數(shù)據(jù)類型，不要建立索引。

（5）如果對(duì)數(shù)據(jù)庫(kù)經(jīng)常執(zhí)行一些插入、刪除等操作時(shí)， 不能建立太多索引。

（6）索引可以跟Where語(yǔ)句的集合融為一體。

當(dāng)創(chuàng)建完索引后， 若SQL查詢語(yǔ)句存在如下情況， 優(yōu)化器生成的執(zhí)行計(jì)劃也會(huì)對(duì)創(chuàng)建好的所有進(jìn)行使用， 因而盡量不要發(fā)生以下狀況。

（1）如果對(duì)某個(gè)表中的兩個(gè)列進(jìn)行比較時(shí)，索引必不會(huì)一直被數(shù)據(jù)庫(kù)使用；

如查詢操作：SELECT EMP_NO，EMP_SALARY FROM EMP WHERE EMP_NO>MGR；

（2）數(shù)據(jù)庫(kù)的表中存在NULL值。一般索引中并不會(huì)有NULL值。如果WHERE條件語(yǔ)句中出現(xiàn)is null或者is not null時(shí)， 索引就無(wú)效，不能被使用；

如下查詢將將不會(huì)對(duì)comm列的索引進(jìn)行訪問(wèn)：

SELECT EMP_NAME，EMP_NO，EMP_SALARY FROM EMP WHERE COMM IS NOT NULL；

（3）在WHERE條件語(yǔ)句中使用了不等于操作符號(hào)，如！=、<>；

SELECT EMP_NAME， EMP_SALARY，EMP_NO，DEPT_NO FROM EMP WHERE DEPT_NO<>20 GROUP BY EMP_NO；

可以改寫為：

SELECT EMP_NAME， EMP_SALARY，EMP_NO，DEPT_NO FROM EMP WHERE DEPT_NO<20 OR DEPT_NO>20 GROUP BY EMP_NO；

（4）當(dāng)對(duì)操作的列使用函數(shù)時(shí)；

SELECT EMP_NAME， EMP_NO， JOB，EMP_SALARY FROM EMP WHERE UPPER（DEPT_NO）=10 GROUP BY EMP_NO；

可以創(chuàng)建基于函數(shù)的索引來(lái)優(yōu)化此查詢。

Create index DEPT_NO_idx on emp（upper（DEPT_NO））；

（5）對(duì)相互不匹配的數(shù)據(jù)類型進(jìn)行比較，DEPT_NO字段類型是VARCHAR（2）， 如查詢：

SELECT EMP_NAME， EMP_NO， EMP_SALARY FROM EMP WHERE DEPT_NO=30；

Oracle數(shù)據(jù)庫(kù)可以自動(dòng)把where條件子句變成TO_NUMBER（DEPT_NO）=30， 這樣就限制了DEPT_NO列上索引的使用。

3.3 重寫SQL語(yǔ)句

3.3.1 使用WHERE代替HAVING

盡量不要使用HAVING條件子句， HAVING在檢索出所有滿足條件的結(jié)果集之后，再對(duì)這些結(jié)果集進(jìn)行篩選。 整個(gè)的處理過(guò)程需要排序，總計(jì)等一系列操作。而WHERE條件子句限制了記錄的數(shù)量，減少資源在這方面的消耗；

例如查詢語(yǔ)句：

SELECT EMP_NAME，EMP_NO， AVG（EMP_SALARY） FROM EMP GROUP BY DEPT_NO HAVING EMP_NO<1000；

對(duì)表EMP中所有列項(xiàng)進(jìn)行匯總之后， 再踢除EMP_NO小于1000的記錄， 可以將上列查詢語(yǔ)句改寫為如下語(yǔ)句：

SELECT EMP_NAME，EMP_NO， AVG（EMP_SALARY） FROM EMP WHERE EMP_NO<1000 GROUP BY DEPT_NO ；

原因是在匯總前排除掉了EMP_NO<1000的記錄，從而提升了查詢語(yǔ)句的執(zhí)行效率。

3.3.2 UNION ALL代替UNION

當(dāng)我們需要UNION兩個(gè)查詢結(jié)果集合時(shí)，這兩個(gè)結(jié)果集將會(huì)以UNION ALL的方式被Oracle數(shù)據(jù)庫(kù)進(jìn)行合并，然后進(jìn)行排序后再輸出結(jié)果。如果使用 UNION ALL替代UNION，就不需要這樣的排序了，將會(huì)提升Oracle數(shù)據(jù)庫(kù)的查詢執(zhí)行的效率。另外特別注意的是，UNION ALL會(huì)重復(fù)輸出兩個(gè)結(jié)果集中相同的記錄。

3.3.3 使用DECODE函數(shù)

使用DECODE函數(shù)能夠避免重復(fù)掃描表中相同的數(shù)據(jù)。

例如：

SELECT COUNT（EMP_NO），SUM（EMP_SALARY） FROM EMP WHERE DEPT_NO = 20 AND EMP_NAME LIKE‘JORDAN%；

SELECT COUNT（EMP_NO），SUM（EMP_SALARY） FROM EMP WHERE DEPT_NO = 30 AND EMP_NAME LIKE‘JORDAN%；

可以使用DECODE函數(shù)高效的得出查詢結(jié)果：

SELECT COUNT（DECODE（DEPT_NO，20，X，NULL））

D20_COUNT，COUNT（DECODE（DEPT_NO，30，X，NULL））

D30_COUNT，SUM（DECODE（DEPT_NO，20，EMP_SALARY，NULL））

D20_EMP_SALARY，SUM（DECODE（DEPT_NO，30，EMP_SALARY，NULL））

D30_EMP_SALARY FROM EMP WHERE EMP_NAME LIKE‘JORDAN%；

3.3.4 用TRANCATE代替DELETE

當(dāng)刪除表中的記錄時(shí)，在一般情形中，回滾段存放的是能夠被恢復(fù)的信息。如果事務(wù)沒(méi)有被提交，Oracle數(shù)據(jù)庫(kù)之中的數(shù)據(jù)將會(huì)恢復(fù)到最初沒(méi)有被刪除的的狀態(tài)，而使用TRUNCATE，被恢復(fù)的信息不再存放于回滾段之中。因而調(diào)用的資源將變得很少，SQL語(yǔ)句執(zhí)行的效率也將變得越快。

3.3.5 物化視圖查詢重寫

所謂物化視圖查詢重寫就是，如果初始化參數(shù)query_rewrite_ enabled設(shè)置為TRUE，并且數(shù)據(jù)庫(kù)運(yùn)行在COST優(yōu)化模式下，當(dāng)對(duì)基表進(jìn)行查詢時(shí)，Oracle會(huì)自動(dòng)判斷是否能利用這個(gè)基表的所有包含ENABLE QUERY REWRITE關(guān)鍵字的物化視圖，如果可以且根據(jù)統(tǒng)計(jì)信息判斷通過(guò)查詢物化視圖代價(jià)更小，則Oracle數(shù)據(jù)庫(kù)的查詢語(yǔ)句將被重寫，通過(guò)查詢物化視圖可以得出正確的查詢結(jié)果。

3.3.6 創(chuàng)建位圖索引

由于索引是位圖，所以很多時(shí)候可以對(duì)這些索引中的位圖進(jìn)行位運(yùn)算（and 和 or），在某些情況下這樣的速度明顯比b樹快；由于位圖索引可以存儲(chǔ)null，所以可以直接通過(guò)位圖索引計(jì)數(shù)。

SELECT EMP_NAME FROM EMP WHERE EMP_SALARY>10000 and JOB=SALES AND DEPT_NO=10；已用時(shí)間：00：00：01.13

在對(duì)EMP_SALARY（月薪）， JOB（職位）， DEPT_NO（部門編號(hào)）三列創(chuàng)建位圖索引， 上面的SQL查詢語(yǔ)句在執(zhí)行時(shí)話費(fèi)時(shí)間為：00：00：00.08， 對(duì)比兩次執(zhí)行所消耗的時(shí)間，SQL執(zhí)行的效率的得到提升，若將這種方式使用到實(shí)際應(yīng)用當(dāng)中，數(shù)據(jù)庫(kù)性能的提升等級(jí)不言而喻。

4 備注

本文所用的表為EMP（員工表），表中字段結(jié)構(gòu)為EMP_NO（員工編號(hào)）、EMP_NAME（員工姓名）、DEPT_NO（所屬部門編號(hào)）、EMP_SALARY（工資）、JOB（職位）等；DEPT（部門表），表中字段結(jié)構(gòu)為DEPT_NAME（部門名稱），DEPT_NO（部門編號(hào)），DEPT_LOC（部門所在城市）等。

【參考文獻(xiàn)】

[1]張學(xué)義，王觀玉，黃雋.基于Oralce數(shù)據(jù)庫(kù)SQL查詢優(yōu)化研究[J].制造業(yè)自動(dòng)化，2011（02）.

[2]劉星.Oracle數(shù)據(jù)庫(kù)的性能優(yōu)化與調(diào)整[J].科技資訊，2011（04）.

[3]張學(xué)琴. 基于Oracle數(shù)據(jù)庫(kù)的SQL語(yǔ)句優(yōu)化[J].電腦知識(shí)與技術(shù)，2010（01）.

[責(zé)任編輯：湯靜]