oracle語句優(yōu)化53個(gè)規(guī)則詳解

oracle優(yōu)化

1.選用適合的ORACLE優(yōu)化器

ORACLE的優(yōu)化器共有3種：

a.RULE(基于規(guī)則)

b.COST(基于成本)

c.CHOOSE(選擇性)

設(shè)置缺省的優(yōu)化器，可以通過對(duì)init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明，如

RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你當(dāng)然也在SQL句級(jí)或是會(huì)話(session)

級(jí)對(duì)其進(jìn)行覆蓋。

為了使用基于成本的優(yōu)化器(CBO,Cost-BasedOptimizer),你必須經(jīng)常運(yùn)行analyze

命令，以增加數(shù)據(jù)庫中的對(duì)象統(tǒng)計(jì)信息(objectstatistics)的準(zhǔn)確性。

如果數(shù)據(jù)庫的優(yōu)化器模式設(shè)置為選擇性(CHOOSE),那么實(shí)際的優(yōu)化器模式將和是否運(yùn)

行過analyze命令有關(guān)。如果table已經(jīng)被analyze過，優(yōu)化器模式將自動(dòng)成為CB。，反

之，數(shù)據(jù)庫將采用RULE形式的優(yōu)化器。

在缺省情況下，ORACLE采用CHOOSE優(yōu)化器，為了避免那些不必要的全表掃描(fulltable

scan),你必須盡量避免使用CHOOSE優(yōu)化器，而直接采用基于規(guī)則或者基于成本的優(yōu)化

器。

2.訪問Table的方式ORACLE采用兩種訪問表中記錄的方式：

a.全表掃描

全表掃描就是順序地訪問表中每條記錄。ORACLE采用一次讀入多個(gè)數(shù)據(jù)塊

(databaseblock)的方式優(yōu)化全表掃描。

b.通過ROWID訪問表

你可以采用基于ROWID的訪問方式情況，提高訪問表的效率，ROWID包含了表中

記錄的物理位置信息……ORACLE采用索引(INDEX)實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置

(ROWID)之間的聯(lián)系。通常索引提供了快速訪問ROWID的方法，因此那些基于索引列的

查詢就可以得到性能上的提高。

3.共享SQL語句

為了不重復(fù)解析相同的SQL語句，在第一次解析之后，ORACLE將SQL語句存放在內(nèi)存

中。這塊位于系統(tǒng)全局區(qū)域SGA(systemglobalarea)的共享池(sharedbufferpool)中的

內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享。因此，當(dāng)你執(zhí)行一個(gè)SQL語句(有時(shí)被稱為一個(gè)游標(biāo))

時(shí)，如果它和之前的執(zhí)行過的語句完全相同，ORACLE就能很快獲得已經(jīng)被解析的語句以及

最好的執(zhí)行路徑。ORACLE的這個(gè)功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用。

可惜的是ORACLE只對(duì)簡(jiǎn)單的表提供高速緩沖(cachebuffering),這個(gè)功能并不適用

于多表連接查詢。

數(shù)據(jù)庫管理員必須在init.ora中為這個(gè)區(qū)域設(shè)置合適的參數(shù)，當(dāng)這個(gè)內(nèi)存區(qū)域越大，就可

以保留更多的語句，當(dāng)然被共享的可能性也就越大了。

當(dāng)你向ORACLE提交一個(gè)SQL語句，ORACLE會(huì)首先在這塊內(nèi)存中查找相同的語句。

這里需要注明的是，ORACLE對(duì)兩者采取的是一種嚴(yán)格匹配，要達(dá)成共享，SQL語句必

須完全相同(包括空格，換行等)。

共享的語句必須滿足三個(gè)條件：

A.字符級(jí)的比較：

當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同。

例如:

SELECT*FROMEMP；

和下列每一個(gè)都不同

SELECT*fromEMP；

Select*FromEmp；

SELECT*FROMEMP；

B.兩個(gè)語句所指的對(duì)象必須完全相同:

例如：

用戶對(duì)象名如何訪問

Jacksaljimitprivatesynonym

Work_citypublicsynonym

Plant_detailpublicsynonym

Jillsaljimitprivatesynonym

Work_citypublicsynonym

Plantdetailtableowner

考慮一下下列SQL語句能否在這兩個(gè)用戶之間共享。

SQL能否共享原因

selectmax(sal_cap)fromsaljimit;不能每個(gè)用戶都有一個(gè)privatesynonym-saljimit,它們

是不同的對(duì)象

selectcount(*0fromwork_citywheresdesclike'NEW%';能兩個(gè)用戶訪問相同的對(duì)象public

synonym-work_city

selecta.sdesc,b.locationfromwork_citya,plant_detailbwherea.cityjd=b.cityjd不能用戶

jack通過privatesynonym訪問plant_detail而jill是表的所有者，對(duì)象不同.

兩個(gè)SQL語句中必須使用相同的名字的綁定變量（bindvariables）

例如：第一組的兩個(gè)SQL語句是相同的（可以共享），而第二組中的兩個(gè)語句是不同的

（即使在運(yùn)行時(shí)，賦于不同的綁定變量相同的值）

a.

selectpin,namefrompeoplewherepin=：blkl.pin；

selectpin,namefrompeoplewherepin=：blkl.pin；

b.

selectpin,namefrompeoplewherepin=：blkl.otjnd；

selectpin,namefrompeoplewherepin=：blkl.ovjnd；

4.選擇最有效率的表名順序（只在基于規(guī)則的優(yōu)化器中有效）

ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在

最后的表（基礎(chǔ)表drivingtable）將被最先處理。在FROM子句中包含多個(gè)表的情況下，

你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。當(dāng)ORACLE處理多個(gè)表時(shí)，會(huì)運(yùn)用排序及合并

的方式連接它們。首先，掃描第一個(gè)表（FROM子句中最后的那個(gè)表）并對(duì)記錄進(jìn)行派序，

然后掃描第二個(gè)表（FROM子句中最后第二個(gè)表），最后將所有從第二個(gè)表中檢索出的記錄

與第一個(gè)表中合適記錄進(jìn)行合并。

例如：

表TAB116,384條記錄

表TAB21條記錄

選擇TAB2作為基礎(chǔ)表（最好的方法）

selectcount(*)fromtabi,tab2執(zhí)行時(shí)間0.96秒

選擇TAB2作為基礎(chǔ)表(不佳的方法)

selectcount(*)fromtab2,tabi執(zhí)行時(shí)間26.09秒

如果有3個(gè)以上的表連接查詢，那就需要選擇交叉表(intersectiontable)作為基礎(chǔ)

表，交叉表是指那個(gè)被其他表所引用的表。

例如：EMP表描述了LOCATION表和CATEGORY表的交集。

SELECT*

FROMLOCATIONL,

CATEGORYC,

EMPE

WHEREE.EMP_NOBETWEEN1000AND2000

ANDE.CAT_NO=C.CAT_NO

ANDE.LOCN=L.LOCN

將比下列SQL更有效率

SELECT*

FROMEMPE,

LOCATIONL,

CATEGORYC

WHEREE.CAT_NO=C.CAT_NO

ANDE.LOCN=L.LOCN

ANDE.EMP_NOBETWEEN1000AND2000

5.WHERE子句中的連接順序。

ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個(gè)原理，表之間的連接必須寫在

其他WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。

例如：

(低效，執(zhí)行時(shí)間156.3秒)

SELECT...

FROMEMPE

WHERESAL>50000

ANDJOB='MANAGER'

AND25<(SELECTCOUNT(*)FROMEMP

WHEREMGR=E.EMPNO);

(高效，執(zhí)行時(shí)間10.6秒)

SELECT...

FROMEMPE

WHERE25<(SELECTCOUNT)*)FROMEMP

WHEREMGR=E.EMPNO)

ANDSAL>50000

ANDJOB='MANAGER';

6.SELECT子句中避免使用'*'

當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí)，使用動(dòng)態(tài)SQL列引用'*'是一個(gè)方

便的方法。不幸的是，這是一個(gè)非常低效的方法。實(shí)際上，ORACLE在解析的過程中，會(huì)

將‘*'依次轉(zhuǎn)換成所有的列名，這個(gè)工作是通過查詢數(shù)據(jù)字典完成的，這意味著將耗費(fèi)

更多的時(shí)間。

7.減少訪問數(shù)據(jù)庫的次數(shù)

當(dāng)執(zhí)行每條SQL語句時(shí)，ORACLE在內(nèi)部執(zhí)行了許多工作：解析SQL語句，估算索引

的利用率，綁定變量，讀數(shù)據(jù)塊等等。由此可見，減少訪問數(shù)據(jù)庫的次數(shù)，就能實(shí)

際上減少ORACLE的工作量。

例如，以下有三種方法可以檢索出雇員號(hào)等于0342或0291的職員。

方法1(最低效)

SELECTEMP_NAME,SALARY,GRADE

FROMEMP

WHEREEMP_NO=342;

SELECTEMP_NAMEZSALARY,GRADE

FROMEMP

WHEREEMP_NO=291;

方法2(次低效)

DECLARE

CURSORCl(E_NONUMBER)IS

SELECTEMP_NAME,SALARYGRADE

FROMEMP

WHEREEMP_NO=E_NO;

BEGIN

OPENCl(342);

FETCHClINTO

OPENCl(291);

FETCHClINTO；

CLOSECl;

END;

方法3(高效)

SELECTA.EMP_NAME,A.SALARY,A.GRADE,

B.EMP_NAME,B.SALARY,B.GRADE

FROMEMPA,EMPB

WHEREA.EMPNO=342

ANDB.EMP_NO=291;

注意:

在SQL*Plus,SQL*Forms和Pro*C中重新設(shè)置ARRAYSIZE參數(shù)，可以增加每次數(shù)據(jù)庫

訪問的檢索數(shù)據(jù)量，建議值為200.

8.使用DECODE函數(shù)來減少處理時(shí)間

使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表。

例如:

SELECTCOUNT(*),SUM(SAL)

FROMEMP

WHEREDEPT_NO=0020

ANDENAMELIKE'SMITH%';

SELECTCOUNT(*),SUM(SAL)

FROMEMP

WHEREDEPT_NO=0030

ANDENAMELIKE'SMITH%';

你可以用DECODE函數(shù)高效地得到相同結(jié)果

SELECTCOUNT(DECODE(DEPT_NO,O020/X；NULL))D0020_COUNT,

COUNT(DECODE(DEPT_NO,0030；X',NULL))D0030_COUNTZ

SUM(DECODE(DEPT_NO,0020,SAL,NULL))D0020_SALz

SUM(DECODE(DEPT_NO,0030,SAL,NULL))D0030_SAL

FROMEMPWHEREENAMELIKE'SMITH%';

類似的，DECODE函數(shù)也可以運(yùn)用于GROUPBY和ORDERBY子句中。

9.整合簡(jiǎn)單，無關(guān)聯(lián)的數(shù)據(jù)庫訪問

如果你有幾個(gè)簡(jiǎn)單的數(shù)據(jù)庫查詢語句，你可以把它們整合到一個(gè)查詢中(即使它們之間沒

有關(guān)系)

例如：

SELECTNAME

FROMEMP

WHEREEMP_NO=1234;

SELECTNAME

FROMDPT

WHEREDPT_NO=10;

SELECTNAME

FROMCAT

WHERECAT_TYPE='RD';

上面的3個(gè)查詢可以被合并成一個(gè)：

SELECTE.NAME,D.NAME,C.NAME

FROMCATC,DPTD,EMPE,DUALX

WHERENVL(XX.DUMMY)=NVL(XE.ROWID(+))

ANDNVL(XZX.DUMMY)=NVL(%D.ROWID(+))

ANDNVL(XX.DUMMY)=NVL(%C.ROWID(+))

ANDE.EMP_NO(+)=1234

ANDD.DEPT_NO(+)=10

ANDC.CAT_TYPE(+)='RD';

(譯者按：雖然采取這種方法，效率得到提高，但是程序的可讀性大大降低，所以讀者還

是要權(quán)衡之間的利弊)

10.刪除重復(fù)記錄

最高效的刪除重復(fù)記錄方法(因?yàn)槭褂昧薘OWID)

DELETEFROMEMPE

WHEREE.ROWID>(SELECTMIN(X.ROWID)

FROMEMPX

WHEREX.EMP_NO=E.EMP_NO);

11.用TRUNCATE替代DELETE

當(dāng)刪除表中的記錄時(shí)，在通常情況下，回滾段(rollbacksegments)用來存放可以被

恢復(fù)的信息。如果你沒有COMMIT事務(wù)，ORACLE會(huì)將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確

地說是恢復(fù)到執(zhí)行刪除命令之前的狀況)

而當(dāng)運(yùn)用TRUNCATE時(shí)，回滾段不再存放任何可被恢復(fù)的信息。當(dāng)命令運(yùn)行后，數(shù)據(jù)

不能被恢復(fù)。因此很少的資源被調(diào)用，執(zhí)行時(shí)間也會(huì)很短。

(譯者按：TRUNCATE只在刪除全表適用，TRUNCATE是DDL不是DML)

12.盡量多使用COMMIT

只要有可能，在程序中盡量多使用COMMIT,這樣程序的性能得到提高，需求也會(huì)因?yàn)?/p>

COMMIT■所釋放的資源而減少：COMMIT"所釋放的資源：

a.回滾段上用于恢復(fù)數(shù)據(jù)的信息。

b.被程序語句獲得的鎖

redologbuffer中的空間

d.ORACLE為管理上述3種資源中的內(nèi)部花費(fèi)

(譯者按：在使用COMMIT時(shí)必須要注意到事務(wù)的完整性，現(xiàn)實(shí)中效率和事務(wù)完整性往

往是魚和熊掌不可得兼)

13.計(jì)算記錄條數(shù)

和一般的觀點(diǎn)相反，count(*)比count(1)稍快，當(dāng)然如果可以通過索引檢索，對(duì)

索引列的計(jì)數(shù)仍舊是最快的。例如COUNT(EMPNO)

(譯者按：在CSDN論壇中，曾經(jīng)對(duì)此有過相當(dāng)熱烈的討論，作者的觀點(diǎn)并不十分準(zhǔn)

確，通過實(shí)際的測(cè)試，上述三種方法并沒有顯著的性能差別)

14.用Where子句替換HAVING子句

避免使用HAVING子句，HAVING只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過濾。這個(gè)

處理需要排序，總計(jì)等操作。如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面

的開銷。

例如:

低效：

SELECTREGION,AVG(LOG_SIZE)

FROMLOCATION

GROUPBYREGION

HAVINGREGIONREGION!=SYDNEY'

ANDREGION!='PERTH'

高效

SELECTREGION,AVG(LOG_SIZE)

FROMLOCATION

WHEREREGIONREGION—SYDNEY'

ANDREGION-'PERTH'

GROUPBYREGION

(譯者按：HAVING中的條件一般用于對(duì)一些集合函數(shù)的比較，如COUNT()等等。除

此而外，一般的條件應(yīng)該寫在WHERE子句中)

15.減少對(duì)表的查詢

在含有子查詢的SQL語句中，要特別注意減少對(duì)表的查詢。

例如：

低效

SELECTTAB_NAME

FROMTABLES

WHERETAB_NAME=(SELECTTAB_NAME

FROMIAB_COLUMNS

WHEREVERSION=604)

ANDDB_VER=(SELECTDB_VER

FROMTAB_COLUMNS

WHEREVERSION=604)

高效

SELECTTAB_NAME

FROMTABLES

WHERE(TAB_NAME,DB_VER)

(SELECTTAB_NAME,DB_VER)

FROMTAB_COLUMNS

WHEREVERSION=604)

Update多個(gè)Column例子：

低效：

UPDATEEMP

SETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),

SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)

WHEREEMP_DEPT=0020;

高效:

UPDATEEMP

SET(EMP_CATSAL_RANGE)

=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)

FROMEMP_CATEGORIES)

WHEREEMP_DEPT=0020;

16.通過內(nèi)部函數(shù)提高SQL效率。

SELECTH.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)

FROMHISTORY_TYPETEMPE,EMP_HISTORYH

WHEREH.EMPNO=E.EMPNO

ANDH.HIST_TYPE=T.HIST_TYPE

GROUPBYH.EMPNO,E.ENAME,H.HIST_TYPE/T.TYPE_DESC;

通過調(diào)用下面的函數(shù)可以提高效率。

FUNCTIONLOOKUP_HIST_TYPE(TYPINNUMBER)RETURNVARCHAR2

AS

TDESCVARCHAR2(30);

CURSORClIS

SELECTTYPE_DESC

FROMHISTORY_TYPE

WHEREHIST_TYPE=TYP;

BEGIN

OPENCl;

FETCHClINTOTDESC;

CLOSECl;

RETURN(NVL(TDESC；?/));

END;

FUNCTIONLOOKUP_EMP(EMPINNUMBER)RETURNVARCHAR2

AS

ENAMEVARCHAR2(30);

CURSORClIS

SELECTENAME

FROMEMP

WHEREEMPNO=EMP;

BEGIN

OPENCl;

FETCHClINTOENAME;

CLOSECl;

RETURN(NVL(ENAME；?/));

END;

SELECTH.EMPNO,LOOKUP_EMP(H.EMPNO),

H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)

FROMEMP_HISTORYH

GROUPBYH.EMPNO,H.HIST_TYPE;

(譯者按：經(jīng)常在論壇中看到如'能不能用一個(gè)SQL寫出…。'的貼子，殊不知復(fù)雜的

SQL往往犧牲了執(zhí)行效率。能夠掌握上面的運(yùn)用函數(shù)解決問題的方法在實(shí)際工作中是非常

有意義的)

17.使用表的別名（Alias）

當(dāng)在SQL語句中連接多個(gè)表時(shí)，請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上.這

樣一來，就可以減少解析的時(shí)間并減少那些由Column歧義引起的語法錯(cuò)誤。

（譯者注：Column歧義指的是由于SQL中不同的表具有相同的Column名，當(dāng)SQL語

句中出現(xiàn)這個(gè)Column時(shí)，SQL解析器無法判斷這個(gè)Column的歸屬）

18.用EXISTS替代IN

在許多基于基礎(chǔ)表的查詢中，為了滿足一個(gè)條件，往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接。在這種

情況下，使用EXISTS（或NOTEXISTS）通常將提高查詢的效率。

低效：

SELECT*

FROMEMP（基礎(chǔ)表）

WHEREEMPNO>0

ANDDEPTNOIN（SELECTDEPTNO

FROMDEPT

WHERELOC='MELB'）

高效：

SELECT*

FROMEMP（基礎(chǔ)表）

WHEREEMPNO>0

ANDEXISTS(SELECTX

FROMDEPT

WHEREDEPT.DEPTNO=EMP.DEPTNO

ANDLOC='MELB')

(譯者按：相對(duì)來說，用NOTEXISTS替換NOTIN將更顯著地提高效率，下一節(jié)中將

指出)

19.用NOTEXISTS替代NOTIN

在子查詢中，NOTIN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并。無論在哪種情況下，NOTIN

都是最低效的(因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷)。為了避免使用NOTIN,

我們可以把它改寫成外連接(OuterJoins)或NOTEXISTS.

例如：

SELECT...

FROMEMP

WHEREDEPT_NONOTIN(SELECTDEPT_NO

FROMDEPT

WHEREDEPT_CAT='A');

為了提高效率。改寫為：

(方法一：高效)

SELECT....

FROMEMPA,DEPTB

WHEREA.DEPT_NO=B.DEPT(+)

ANDB.DEPT_NOISNULL

ANDB.DEPT_CAT(+)='A'

(方法二：最高效)

SELECT....

FROMEMPE

WHERENOTEXISTS（SELECTX

FROMDEPTD

WHERED.DEPT_NO=E.DEPT_NO

ANDDEPT_CAT=W）;

20.用表連接替換EXISTS

通常來說，采用表連接的方式比EXISTS更有效率

SELECTENAME

FROMEMPE

WHEREEXISTS（SELECT'X’

FROMDEPT

WHEREDEPT_NO=E.DEPT_NO

ANDDEPT_CAT=W）;

（更高效）

SELECTENAME

FROMDEPTD,EMPE

WHEREE.DEPT_NO=D,DEPT_NO

ANDDEPT_CAT='A';

（譯者按：在RBO的情況下，前者的執(zhí)行路徑包括FILTER,后者使用NESTEDLOOP）

21.用EXISTS替換DISTINCT

當(dāng)提交一個(gè)包含一對(duì)多表信息（比如部門表和雇員表）的查詢時(shí)，避免在SELECT子句中

使用DISTINCT.一般可以考慮用EXIST替換

例如:

低效:

SELECTDISTINCTDEPT_NO,DEPT_NAME

FROMDEPTD,EMPE

WHERED.DEPT_NO=E.DEPT_NO

高效：

SELECTDEPT_NO,DEPT_NAME

FROMDEPTD

WHEREEXISTS(SELECTT

FROMEMPE

WHEREE.DEPT_NO=D.DEPT_NO);

EXISTS使查詢更為迅速，因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回

結(jié)果。

22.識(shí)別'低效執(zhí)行’的SQL語句

用下列SQL工具找出低效SQL：

SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,

ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,

ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,

SQL_TEXT

FROMV$SQLAREA

WHEREEXECUTIONS>0

ANDBUFFER_GETS>0

AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8

ORDERBY4DESC;

（譯者按：雖然目前各種關(guān)于SQL優(yōu)化的圖形化工具層出不窮，但是寫出自己的SQL工

具來解決問題始終是一個(gè)最好的方法）

23.使用TKPROF工具來查詢SQL性能狀態(tài)

SQLtrace工具收集正在執(zhí)行的SQL的性能狀態(tài)數(shù)據(jù)并記錄到一個(gè)跟蹤文件中。這個(gè)跟蹤

文件提供了許多有用的信息，例如解析次數(shù)。執(zhí)行次數(shù)，CPU使用時(shí)間等。這些數(shù)據(jù)將可以

用來優(yōu)化你的系統(tǒng)。

設(shè)置SQLTRACE在會(huì)話級(jí)別：

有效

ALTERSESSIONSETSQL_TRACETRUE

設(shè)置SQLTRACE在整個(gè)數(shù)據(jù)庫有效仿，你必須將SQL_TRACE參數(shù)在init.ora中設(shè)為TRUE,

USER_DUMP_DEST參數(shù)說明了生成跟蹤文件的目錄

（譯者按：這一節(jié)中，作者并沒有提到TKPROF的用法，對(duì)SQLTRACE的用法也不夠準(zhǔn)

確，設(shè)置SQLTRACE首先要在init.ora中設(shè)定TIMED_STATISTICS,這樣才能得到那些重要

的時(shí)間狀態(tài)。生成的trace文件是不可讀的，所以要用TKPROF工具對(duì)其進(jìn)行轉(zhuǎn)換，TKPROF

有許多執(zhí)行參數(shù)。大家可以參考ORACLE手冊(cè)來了解具體的配置。）

24.用EXPLAINPLAN分析SQL語句

EXPLAINPLAN是一個(gè)很好的分析SQL語句的工具，它甚至可以在不執(zhí)行SQL的情況下分

析語句。通過分析，我們就可以知道ORACLE是怎么樣連接表，使用什么方式掃描表（索

引掃描或全表掃描）以及使用到的索引名稱。

你需要按照從里到外，從上到下的次序解讀分析的結(jié)果。EXPLAINPLAN分析的結(jié)果是

用縮進(jìn)的格式排列的，最內(nèi)部的操作將被最先解讀，如果兩個(gè)操作處于同一層中，帶有最

小操作號(hào)的將被首先執(zhí)行。

NESTEDLOOP是少數(shù)不按照上述規(guī)則處理的操作，正確的執(zhí)行路徑是檢查對(duì)NESTED

LOOP提供數(shù)據(jù)的操作，其中操作號(hào)最小的將被最先處理。

譯者按：通過實(shí)踐，感到還是用SQLPLUS中的SETTRACE功能比較方便。

舉例：

SQL>list

1SELECT*

2FROMdept,emp

3*WHEREemp.deptno=dept.deptno

SQL>setautotracetraceonly/*traceonly可以不顯示執(zhí)行結(jié)果*/

SQL>/

14rowsselected.

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE

10NESTEDLOOPS

21TABLEACCESS(FULL)OF'EMP'

31TABLEACCESS(BYINDEXROWID)OF'DEPT'

43INDEX(UNIQUESCAN)OF?PK.DEPT'(UNIQUE)

Statistics

0recursivecalls

2dbblockgets

30consistentgets

0physicalreads

0redosize

2598bytessentviaSQL*Nettoclient

503bytesreceivedviaSQL*Netfromclient

2SQL*Netroundtripsto/fromclient

0sorts(memory)

0sorts(disk)

14rowsprocessed

通過以上分析，可以得出實(shí)際的執(zhí)行步驟是：

1.TABLEACCESS(FULL)OF'EMP'

2.INDEX(UNIQUESCAN)OF'PK_DEPT'(UNIQUE)

3.TABLEACCESS(BYINDEXROWID)OF'DEPT'

4.NESTEDLOOPS(JOINING1AND3)

注：目前許多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQLAnalyze

都提供了極其方便的EXPLAINPLAN工具。也許喜歡圖形化界面的朋友們可以選用它們。

25.用索引提高效率

索引是表的一個(gè)概念部分，用來提高檢索數(shù)據(jù)的效率。實(shí)際上，ORACLE使用了一個(gè)復(fù)

雜的自平衡B-tree結(jié)構(gòu)。通常，通過索引查詢數(shù)據(jù)比全表掃描要快。當(dāng)ORACLE找出執(zhí)行

查詢和Update語句的最佳路徑時(shí)，ORACLE優(yōu)化器將使用索引。同樣在聯(lián)結(jié)多個(gè)表時(shí)使用

索引也可以提高效率。另一個(gè)使用索引的好處是，它提供了主鍵(primarykey)的唯一性

驗(yàn)證.

除了那些LONG或LONGRAW數(shù)據(jù)類型，你可以索引幾乎所有的列。通常，在大型

表中使用索引特別有效。當(dāng)然，你也會(huì)發(fā)現(xiàn)，在掃描小表時(shí)，使用索引同樣能提高效率。

雖然使用索引能得到查詢效率的提高，但是我們也必須注意到它的代價(jià)。索引需要空

間來存儲(chǔ)，也需要定期維護(hù)，每當(dāng)有記錄在表中增減或索引列被修改時(shí)，索引本身也會(huì)被

修改。這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出4,5次的磁盤

I/O.因?yàn)樗饕枰~外的存儲(chǔ)空間和處理，那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變

慢?

譯者按：定期的重構(gòu)索引是有必要的。

ALTERINDEX<INDEXNAME>REBUILD<TABLESPACENAME>

26.索引的操作

ORACLE對(duì)索引有兩種訪問模式。

索引唯一掃描(INDEXUNIQUESCAN)

大多數(shù)情況下，優(yōu)化器通過WHERE子句訪問INDEX.

例如：

表LODGING有兩個(gè)索引：建立在LODGING列上的唯一性索引LODGING_PK和建立在

MANAGER列上的非唯一性索引LODGING$MANAGER.

SELECT*

FROMLODGING

WHERELODGING=4ROSEHILL'；

在內(nèi)部，上述SQL將被分成兩步執(zhí)行，首先，LODGING_PK索引將通過索引唯一

掃描的方式被訪問，獲得相對(duì)應(yīng)的ROWID,通過ROWID訪問表的方式執(zhí)行下一步檢索。

如果被檢索返回的列包括在INDEX列中，ORACLE將不執(zhí)行第二步的處理(通過ROWID

訪問表)。因?yàn)闄z索數(shù)據(jù)保存在索引中，單單訪問索引就可以完全滿足查詢結(jié)果。

下面SQL只需要INDEXUNIQUESCAN操作。

SELECTLODGING

FROMLODGING

WHERELODGING='ROSEHILL'；

索引范圍查詢(INDEXRANGESCAN)

適用于兩種情況：

1.基于一個(gè)范圍的檢索

2.基于非唯一性索引的檢索

例1：

SELECTLODGINGFROMLODGINGWHERELODGINGLIKE'M%'；

WHERE子句條件包括一系列值，ORACLE將通過索引范圍查詢的方式查詢LODGING_PK.

由于索引范圍查詢將返回一組值，它的效率就要比索引唯一掃描低一些。

例2：

SELECTLODGING

FROMLODGING

WHEREMANAGER='BILLGATES);

這個(gè)SQL的執(zhí)行分兩步，LODGING$MANAGER的索引范圍查詢(得到所有符合條件記

錄的ROWID)和下一步同過ROWID訪問表得至ljLODGING歹I」的值。由于LODGING$MANAGER

是一個(gè)非唯一性的索引，數(shù)據(jù)庫不能對(duì)它執(zhí)行索引唯一掃描。

由于SQL返回LODGING歹U，而它并不存在于LODGING$MANAGER索引中，所以在索引

范圍查詢后會(huì)執(zhí)行一個(gè)通過ROWID訪問表的操作。

WHERE子句中，如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通配符(WILDCARD)開始，索

引將不被采用。在這種情況下，ORACLE將使用全表掃描。

SELECTLODGING

FROMLODGING

WHEREMANAGERLIKE'%HANMAN'；

27.基礎(chǔ)表的選擇

基礎(chǔ)表(DrivingTable)是指被最先訪問的表(通常以全表掃描的方式被訪問)。根據(jù)

優(yōu)化器的不同，SQL語句中基礎(chǔ)表的選擇是不一樣的。

如果你使用的是CBO(COSTBASEDOPTIMIZER),優(yōu)化器會(huì)檢查SQL語句中的每個(gè)表

的物理大小，索引的狀態(tài)，然后選用花費(fèi)最低的執(zhí)行路徑。

如果你用RB。(RULEBASEDOPTIMIZER),并且所有的連接條件都有索引對(duì)應(yīng)，在

這種情況下，基礎(chǔ)表就是FROM子句中列在最后的那個(gè)表。

舉例：

SELECTA.NAME,B.MANAGER

FROMWORKERA,

LODGINGB

WHEREA.LODGING=B.LODING：

由于LODGING表的LODING列上有一個(gè)索引，而且WORKER表中沒有相比較的索引，

WORKER表將被作為查詢中的基礎(chǔ)表。

28.多個(gè)平等的索引

當(dāng)SQL語句的執(zhí)行路徑可以使用分布在多個(gè)表上的多個(gè)索引時(shí)，ORACLE會(huì)同時(shí)使用多個(gè)

索引并在運(yùn)行時(shí)對(duì)它們的記錄進(jìn)行合并，檢索出僅對(duì)全部索引有效的記錄。

在ORACLE選擇執(zhí)行路徑時(shí)，唯一性索引的等級(jí)高于非唯一性索引。然而這個(gè)規(guī)則只

有當(dāng)WHERE子句中索引列和常量比較才有效。如果索引列和其他表的索引類相比較。這種

子句在優(yōu)化器中的等級(jí)是非常低的。

如果不同表中兩個(gè)想同等級(jí)的索引將被引用，F(xiàn)ROM子句中表的順序?qū)Q定哪個(gè)會(huì)被

率先使用。FROM子句中最后的表的索引將有最高的優(yōu)先級(jí)。

如果相同表中兩個(gè)想同等級(jí)的索引將被引用，WHERE子句中最先被引用的索引將有最

高的優(yōu)先級(jí)。

舉例：

DEPTNO上有一個(gè)非唯一性索引，EMP_CAT也有一個(gè)非唯一性索引。

SELECTENAME,

FROMEMP

WHEREDEPT_NO=20

ANDEMP_CAT='A'；

這里，DEPTNO索引將被最先檢索，然后同EMP_CAT索引檢索出的記錄進(jìn)行合并。執(zhí)行

路徑如下：

TABLEACCESSBYROWIDONEMP

AND-EQUAL

INDEXRANGESCANONDEPTJDX

INDEXRANGESCANONCATJDX

29.等式比較和范圍比較

當(dāng)WHERE子句中有索引列，ORACLE不能合并它們，ORACLE將用范圍比較。

舉例：

DEPTNO上有一個(gè)非唯一性索引，EMP_CAT也有一個(gè)非唯一性索引。

SELECTENAME

FROMEMP

WHEREDEPTNO>20

ANDEMP_CAT='A'；

這里只有EMP_CAT索引被用到，然后所有的記錄將逐條與DEPTNO條件進(jìn)行比較。執(zhí)

行路徑如下：

TABLEACCESSBYROWIDONEMP

INDEXRANGESCANONCAT_IDX

30.不明確的索引等級(jí)

當(dāng)ORACLE無法判斷索引的等級(jí)高低差別，優(yōu)化器將只使用一個(gè)索引，它就是在WHERE

子句中被列在最前面的。

舉例：

DEPTNO上有一個(gè)非唯一性索引，EMP_CAT也有一個(gè)非唯一性索引。

SELECTENAME

FROMEMP

WHEREDEPTNO>20

ANDEMP_CAT>'A'；

這里，ORACLE只用到了DEPT_NO索引。執(zhí)行路徑如下：

TABLEACCESSBYROWIDONEMP

INDEXRANGESCANONDEPTJDX

譯者按：我們來試一下以下這種情況：

SQL>selectindex_name,uniquenessfromuserjndexeswheretable__name='EMP'；

INDEX_NAMEUNIQUENES

EMPNOUNIQUE

EMPTYPENONUNIQUE

SQL>select*fromempwhereempno>=2andemp__type='A1；

norowsselected

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE

10TABLEACCESS(BYINDEXROWID)OF,EMP,

21INDEX(RANGESCAN)OF'EMPTYPE'(NON-UNIQUE)

雖然EMPNO是唯一性索引，但是由于它所做的是范圍比較，等級(jí)要比非唯一性索引的等式

比較低！

31.強(qiáng)制索引失效

如果兩個(gè)或以上索引具有相同的等級(jí)，你可以強(qiáng)制命令。RACLE優(yōu)化器使用其中的一個(gè)

(通過它，檢索出的記錄數(shù)量少).

舉例：

SELECTENAME

FROMEMP

WHEREEMPNO=7935

ANDDEPTNO+0=10/*DEPTNO上的索引將失效*/

ANDEMP_TYPE11''='A'/*EMP_TYPE上的索引將失效*/

這是一種相當(dāng)直接的提高查詢效率的辦法。但是你必須謹(jǐn)慎考慮這種策略，一般來說,

只有在你希望單獨(dú)優(yōu)化幾個(gè)SQL時(shí)才能采用它。

這里有一個(gè)例子關(guān)于何時(shí)采用這種策略,

假設(shè)在EMP表的EMP_TYPE列上有一個(gè)非唯一性的索引而EMP_CLASS上沒有索引。

SELECTENAME

FROMEMP

WHEREEMP_TYPE='A'

ANDEMP_CLASS='X'；

優(yōu)化器會(huì)注意到EMP_TYPE上的索引并使用它。這是目前唯一的選擇。如果，一段時(shí)

間以后，另一個(gè)非唯一性建立在EMP_CLASS上，優(yōu)化器必須對(duì)兩個(gè)索引進(jìn)行選擇，在通常

情況下，優(yōu)化器將使用兩個(gè)索引并在他們的結(jié)果集合上執(zhí)行排序及合并。然而，如果其中

一個(gè)索引(EMP_TYPE)接近于唯一性而另一個(gè)索引(EMP_CLASS)上有幾千個(gè)重復(fù)的值。排

序及合并就會(huì)成為一種不必要的負(fù)擔(dān)。在這種情況下，你希望使優(yōu)化器屏蔽掉EMP_CLASS

索引。

用下面的方案就可以解決問題。

SELECTENAME

FROMEMP

WHEREEMP_TYPE='A'

ANDEMP_CLASS|I*'='X'；

32.避免在索引列上使用計(jì)算。

WHERE子句中，如果索引列是函數(shù)的一部分。優(yōu)化器將不使用索引而使用全表掃描。

舉例：

低效:

SELECT…

FROMDEPT

WHERESAL*12>25000；

高效：

SELECT…

FROMDEPT

WHERESAL>25000/12；

譯者按：這是一個(gè)非常實(shí)用的規(guī)則，請(qǐng)務(wù)必牢記

33.自動(dòng)選擇索引

如果表中有兩個(gè)以上（包括兩個(gè)）索引，其中有一個(gè)唯一性索引，而其他是非唯一性。

在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引。

舉例：

SELECTENAME

FROMEMP

WHEREEMPNO=2326

ANDDEPTNO=20；

這里，只有EMPNO上的索引是唯一性的，所以EMPNO索引將用來檢索記錄。

TABLEACCESSBYROWIDONEMP

INDEXUNIQUESCANONEMPNOIDX

34.避免在索引列上使用NOT

通常，我們要避免在索引列上使用NOT,NOT會(huì)產(chǎn)生在和在索引列上使用函數(shù)相同的影

響。當(dāng)ORACLE“遇到"NOT,他就會(huì)停止使用索引轉(zhuǎn)而執(zhí)行全表掃描。

舉例：

低效：（這里，不使用索引）

SELECT...

FROMDEPT

WHEREDEPT_CODENOT=0；

高效：（這里，使用了索引）

SELECT...

FROMDEPT

WHEREDEPT_CODE>0；

需要注意的是，在某些時(shí)候，ORACLE優(yōu)化器會(huì)自動(dòng)將NOT轉(zhuǎn)化成相對(duì)應(yīng)的關(guān)系操作

符。

NOT>to<=

NOT>=to<

NOT<to>=

NOT<=to>

譯者按：在這個(gè)例子中，作者犯了一些錯(cuò)誤。例子中的低效率SQL是不能被執(zhí)行的。

我做了一些測(cè)試:

SQL>select*fromempwhereNOTempno>1；

norowsselected

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE

10TABLEACCESS(BYINDEXROWID)OF'EMP'

21INDEX(RANGESCAN)OF'EMPNO'(UNIQUE)

SQL>select*fromempwhereempno<=1；

norowsselected

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE

10TABLEACCESS(BYINDEXROWID)OF'EMP'

21INDEX(RANGESCAN)OF'EMPNO*(UNIQUE)

兩者的效率完全一樣，也許這符合作者關(guān)于“在某些時(shí)候，ORACLE優(yōu)化器會(huì)自動(dòng)將

NOT轉(zhuǎn)化成相對(duì)應(yīng)的關(guān)系操作符”的觀點(diǎn)。

35.用＞=替代〉

如果DEPTNO上有一個(gè)索引，

同j效:

SELECT*

FROMEMP

WHEREDEPTNO>=4

低效：

SELECT*

FROMEMP

WHEREDEPTNO>3

兩者的區(qū)別在于，前者DBMS將直接跳到第一個(gè)DEPT等于4的記錄而后者將首先定

位至I」DEPTNO=3的記錄并且向前掃描到第一個(gè)DEPT大于3的記錄。

36.用UNION替換OR（適用于索引列）

通常情況下，用UNION替換WHERE子句中的OR將會(huì)起到較好的效果。對(duì)索引列使

用OR將造成全表掃描。注意，以上規(guī)則只針對(duì)多個(gè)索引列有效。如果有column沒有被

索引，查詢效率可能會(huì)因?yàn)槟銢]有選擇OR而降低。

在下面的例子中，LOCJD和REGION上都建有索引。

高效：

SELECTLOCJD,LOC_DESC,REGION

FROMLOCATION

WHERELOCJD=10

UNION

SELECTLOCJD,LOC_DESC，REGION

FROMLOCATION

WHEREREGION="MELBOURNE”

低效:

SELECTLOCID，LOC_DESC，REGION

FROMLOCATION

WHERELOCID=10ORREGION="MELBOURNE”

如果你堅(jiān)持要用OR，那就需要返回記錄最少的索引列寫在最前面。

注意:

WHEREKEY1=10（返回最少記錄）

ORKEY2=20（返回最多記錄）

ORACLE內(nèi)部將以上轉(zhuǎn)換為

WHEREKEY1=10AND((NOTKEY1=10)ANDKEY2=20)

譯者按:

下面的測(cè)試數(shù)據(jù)僅供參考：（a=1003返回一條記錄，b二1返回1003條記錄）

SQL>select*fromunionvsor/*lsttest*/

2wherea=1003orb=1；

1003rowsselected.

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE

10CONCATENZVTION

21TABLEACCESS(BYINDEXROWID)OF'UNIONVSOR'

32INDEX(RANGESCAN)OF'UB'(NON-UNIQUE)

41TABLEACCESS(BYINDEXROWID)OF'UNIONVSOR'

54INDEX(RANGESCAN)OF'UA'(NON-UNIQUE)

Statistics

0recursivecalls

0dbblockgets

144consistentgets

0physicalreads

0redosize

63749bytessentviaSQL*Nettoclient

7751bytesreceivedviaSQL*Netfromclient

68SQL*Netroundtripsto/fromclient

0sorts(memory)

0sorts(disk)

1003rowsprocessed

SQL>select*fromunionvsor/*2ndtest*/

2whereb=1ora=1003；

1003rowsselected.

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE

10CONCATENZVTION

21TABLEACCESS(BYINDEXROWID)OF'UNIONVSOR'

32INDEX(RANGESCAN)OF'UA'(NON-UNIQUE)

41TABLEACCESS(BYINDEXROWID)OF'UNIONVSOR'

54INDEX(RANGESCAN)OF'UB'(NON-UNIQUE)

Statistics