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

oracle優(yōu)化

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

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

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

b.COST(基于成本)

c.CHOOSE(選擇性)

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

RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你當然也在SQL句級或是會話(session)

級對其進行覆蓋。

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

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

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

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

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

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

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

器。

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

a.全表掃描

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

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

b.通過ROWID訪問表

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

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

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

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

3.共享SQL語句

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

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

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

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

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

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

于多表連接查詢。

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

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

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

這里需要注明的是，ORACLE對兩者采取的是一種嚴格匹配，要達成共享，SQL語句必

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

共享的語句必須滿足三個條件：

A.字符級的比較：

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

例如:

SELECT*FROMEMP；

和下列每一個都不同

SELECT*fromEMP；

Select*FromEmp；

SELECT*FROMEMP；

B.兩個語句所指的對象必須完全相同:

例如：

用戶對象名如何訪問

Jacksaljimitprivatesynonym

Work_citypublicsynonym

Plant_detailpublicsynonym

Jillsaljimitprivatesynonym

Work_citypublicsynonym

Plantdetailtableowner

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

SQL能否共享原因

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

是不同的對象

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

synonym-work_city

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

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

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

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

（即使在運行時，賦于不同的綁定變量相同的值）

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子句中寫在

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

你必須選擇記錄條數(shù)最少的表作為基礎表。當ORACLE處理多個表時，會運用排序及合并

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

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

與第一個表中合適記錄進行合并。

例如：

表TAB116,384條記錄

表TAB21條記錄

選擇TAB2作為基礎表（最好的方法）

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

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

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

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

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

例如：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ù)這個原理，表之間的連接必須寫在

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

例如：

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

SELECT...

FROMEMPE

WHERESAL>50000

ANDJOB='MANAGER'

AND25<(SELECTCOUNT(*)FROMEMP

WHEREMGR=E.EMPNO);

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

SELECT...

FROMEMPE

WHERE25<(SELECTCOUNT)*)FROMEMP

WHEREMGR=E.EMPNO)

ANDSAL>50000

ANDJOB='MANAGER';

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

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

便的方法。不幸的是，這是一個非常低效的方法。實際上，ORACLE在解析的過程中，會

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

更多的時間。

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

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

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

際上減少ORACLE的工作量。

例如，以下有三種方法可以檢索出雇員號等于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中重新設置ARRAYSIZE參數(shù)，可以增加每次數(shù)據(jù)庫

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

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

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

例如:

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ù)也可以運用于GROUPBY和ORDERBY子句中。

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

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

有關(guān)系)

例如：

SELECTNAME

FROMEMP

WHEREEMP_NO=1234;

SELECTNAME

FROMDPT

WHEREDPT_NO=10;

SELECTNAME

FROMCAT

WHERECAT_TYPE='RD';

上面的3個查詢可以被合并成一個：

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.刪除重復記錄

最高效的刪除重復記錄方法(因為使用了ROWID)

DELETEFROMEMPE

WHEREE.ROWID>(SELECTMIN(X.ROWID)

FROMEMPX

WHEREX.EMP_NO=E.EMP_NO);

11.用TRUNCATE替代DELETE

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

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

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

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

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

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

12.盡量多使用COMMIT

只要有可能，在程序中盡量多使用COMMIT,這樣程序的性能得到提高，需求也會因為

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

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

b.被程序語句獲得的鎖

redologbuffer中的空間

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

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

往是魚和熊掌不可得兼)

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

和一般的觀點相反，count(*)比count(1)稍快，當然如果可以通過索引檢索，對

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

(譯者按：在CSDN論壇中，曾經(jīng)對此有過相當熱烈的討論，作者的觀點并不十分準

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

14.用Where子句替換HAVING子句

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

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

的開銷。

例如:

低效：

SELECTREGION,AVG(LOG_SIZE)

FROMLOCATION

GROUPBYREGION

HAVINGREGIONREGION!=SYDNEY'

ANDREGION!='PERTH'

高效

SELECTREGION,AVG(LOG_SIZE)

FROMLOCATION

WHEREREGIONREGION—SYDNEY'

ANDREGION-'PERTH'

GROUPBYREGION

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

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

15.減少對表的查詢

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

例如：

低效

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多個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)常在論壇中看到如'能不能用一個SQL寫出…。'的貼子，殊不知復雜的

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

有意義的)

17.使用表的別名（Alias）

當在SQL語句中連接多個表時，請使用表的別名并把別名前綴于每個Column上.這

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

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

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

18.用EXISTS替代IN

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

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

低效：

SELECT*

FROMEMP（基礎表）

WHEREEMPNO>0

ANDDEPTNOIN（SELECTDEPTNO

FROMDEPT

WHERELOC='MELB'）

高效：

SELECT*

FROMEMP（基礎表）

WHEREEMPNO>0

ANDEXISTS(SELECTX

FROMDEPT

WHEREDEPT.DEPTNO=EMP.DEPTNO

ANDLOC='MELB')

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

指出)

19.用NOTEXISTS替代NOTIN

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

都是最低效的(因為它對子查詢中的表執(zhí)行了一個全表遍歷)。為了避免使用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

當提交一個包含一對多表信息（比如部門表和雇員表）的查詢時，避免在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使查詢更為迅速，因為RDBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回

結(jié)果。

22.識別'低效執(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工

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

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

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

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

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

設置SQLTRACE在會話級別：

有效

ALTERSESSIONSETSQL_TRACETRUE

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

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

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

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

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

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

24.用EXPLAINPLAN分析SQL語句

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

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

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

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

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

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

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

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

譯者按：通過實踐，感到還是用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

通過以上分析，可以得出實際的執(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.用索引提高效率

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

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

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

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

驗證.

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

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

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

間來存儲，也需要定期維護，每當有記錄在表中增減或索引列被修改時，索引本身也會被

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

I/O.因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應時間變

慢?

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

ALTERINDEX<INDEXNAME>REBUILD<TABLESPACENAME>

26.索引的操作

ORACLE對索引有兩種訪問模式。

索引唯一掃描(INDEXUNIQUESCAN)

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

例如：

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

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

SELECT*

FROMLODGING

WHERELODGING=4ROSEHILL'；

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

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

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

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

下面SQL只需要INDEXUNIQUESCAN操作。

SELECTLODGING

FROMLODGING

WHERELODGING='ROSEHILL'；

索引范圍查詢(INDEXRANGESCAN)

適用于兩種情況：

1.基于一個范圍的檢索

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

例1：

SELECTLODGINGFROMLODGINGWHERELODGINGLIKE'M%'；

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

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

例2：

SELECTLODGING

FROMLODGING

WHEREMANAGER='BILLGATES);

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

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

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

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

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

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

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

SELECTLODGING

FROMLODGING

WHEREMANAGERLIKE'%HANMAN'；

27.基礎表的選擇

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

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

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

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

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

這種情況下，基礎表就是FROM子句中列在最后的那個表。

舉例：

SELECTA.NAME,B.MANAGER

FROMWORKERA,

LODGINGB

WHEREA.LODGING=B.LODING：

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

WORKER表將被作為查詢中的基礎表。

28.多個平等的索引

當SQL語句的執(zhí)行路徑可以使用分布在多個表上的多個索引時，ORACLE會同時使用多個

索引并在運行時對它們的記錄進行合并，檢索出僅對全部索引有效的記錄。

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

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

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

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

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

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

高的優(yōu)先級。

舉例：

DEPTNO上有一個非唯一性索引，EMP_CAT也有一個非唯一性索引。

SELECTENAME,

FROMEMP

WHEREDEPT_NO=20

ANDEMP_CAT='A'；

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

路徑如下：

TABLEACCESSBYROWIDONEMP

AND-EQUAL

INDEXRANGESCANONDEPTJDX

INDEXRANGESCANONCATJDX

29.等式比較和范圍比較

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

舉例：

DEPTNO上有一個非唯一性索引，EMP_CAT也有一個非唯一性索引。

SELECTENAME

FROMEMP

WHEREDEPTNO>20

ANDEMP_CAT='A'；

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

行路徑如下：

TABLEACCESSBYROWIDONEMP

INDEXRANGESCANONCAT_IDX

30.不明確的索引等級

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

子句中被列在最前面的。

舉例：

DEPTNO上有一個非唯一性索引，EMP_CAT也有一個非唯一性索引。

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是唯一性索引，但是由于它所做的是范圍比較，等級要比非唯一性索引的等式

比較低！

31.強制索引失效

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

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

舉例：

SELECTENAME

FROMEMP

WHEREEMPNO=7935

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

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

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

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

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

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

SELECTENAME

FROMEMP

WHEREEMP_TYPE='A'

ANDEMP_CLASS='X'；

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

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

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

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

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

索引。

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

SELECTENAME

FROMEMP

WHEREEMP_TYPE='A'

ANDEMP_CLASS|I*'='X'；

32.避免在索引列上使用計算。

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

舉例：

低效:

SELECT…

FROMDEPT

WHERESAL*12>25000；

高效：

SELECT…

FROMDEPT

WHERESAL>25000/12；

譯者按：這是一個非常實用的規(guī)則，請務必牢記

33.自動選擇索引

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

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

舉例：

SELECTENAME

FROMEMP

WHEREEMPNO=2326

ANDDEPTNO=20；

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

TABLEACCESSBYROWIDONEMP

INDEXUNIQUESCANONEMPNOIDX

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

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

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

舉例：

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

SELECT...

FROMDEPT

WHEREDEPT_CODENOT=0；

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

SELECT...

FROMDEPT

WHEREDEPT_CODE>0；

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

符。

NOT>to<=

NOT>=to<

NOT<to>=

NOT<=to>

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

我做了一些測試:

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)于“在某些時候，ORACLE優(yōu)化器會自動將

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

35.用＞=替代〉

如果DEPTNO上有一個索引，

同j效:

SELECT*

FROMEMP

WHEREDEPTNO>=4

低效：

SELECT*

FROMEMP

WHEREDEPTNO>3

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

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

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

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

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

索引，查詢效率可能會因為你沒有選擇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”

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

注意:

WHEREKEY1=10（返回最少記錄）

ORKEY2=20（返回最多記錄）

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

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

譯者按:

下面的測試數(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