Oraclesql性能優(yōu)化調(diào)整

1.訪問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的方法，因此那些基于索引列的查詢就可以得到性能上的提高.

2.選擇最有效率的表名順序

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è)表中適宜記錄上行合并.

例如：表TAB116,384條記錄

表TAB21條記錄

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

selectcount（*）fromtabi,:ab2執(zhí)行時(shí)間0.96秒

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

selectcount（*）fromtab2,:abl執(zhí)行時(shí)間26.09秒

如果有3個(gè)以上的表連接查詢，那就需要選擇交叉表（inlersectionlable）作為基礎(chǔ)表，交叉表是指那個(gè)被其他

表所引用的表.

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

SELECT*

FROMLOCATIONL,

CATEGORYC,

EMPE

WHEREE.EMP_NOBETWEEN1000AND2000

ANDE.CATNO=C.CATNO

ANDE.LOCN=L.LOCN

將比以下SQL更有效率

SELECT*

FROMEMPE,

LOCATIONL,

CATEGORYC

WHEREE.CAT_NO=C.CAT_NO

ANDE.LOCN=L.LOCN

ANDE.EMP_NOBETWEEN1OOOAND2000

3.WHERE子句中的連接順序.

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

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

例如：

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

SELECT…

FROMEMPI-

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'；

4.SELECT子句中防止使用'?'

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

是一個(gè)非常低效的方法.實(shí)際上,ORACLE在解析的過程中，會(huì)將'*'依次轉(zhuǎn)換成所有的列名，這個(gè)工作是

通過查詢數(shù)據(jù)字典完成的，這意味著將消耗更多的時(shí)間.

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

當(dāng)執(zhí)行每條SQL語(yǔ)句時(shí)，ORACLE在內(nèi)部執(zhí)行了許多工作：解析SQL語(yǔ)句，估算索引的利用率，綁定變量,

讀數(shù)據(jù)塊等等.由此可見，減少訪問數(shù)據(jù)庫(kù)的次數(shù)，就能實(shí)際上減少ORACLE的工作量.

例如，

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

方法1(最低效)

SELECTEMP_NAME,SALARY,GRADE

FROMEMP

WHEREEMP_N0=342；

SELECTEMP_NAME,SALARY,GRADE

FROMEMP

WHEREEMP_NO=291；

方法2(次低效)

DECLARE

CURSORCl(E_NONUMBER)IS

SELECTEMP_NAME,SALARY,GRADE

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.EMP_NO=342

ANDB.EMPNO=291；

注意：

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

量，建議值為20().

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

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

例如:

SELECTCOUNT(*),SUM(SAL)

FROMEMP

WHEREI)I-PT_NO=0020

ANDENAMELIKE'SMITH%'；

SELECTCOUNT(*),SUM(SAL)

FROMEMP

WHEREDEPT_NO=0030

ANDENAMELIKE'SMITH%'；

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

SELItCTCOUNT(DECODE(I)EPT_NO,0020,*X'.NULL))l)0020_C0UNT,

COUNT(DECODE(DEPT_NO,0030,'X'.NULL))D0030_C0UNT,

SUM(DECODE(DEPT_N0,0020,SAL,NULL))D0020.SAL,

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

FROMEMPWHEREENAMELIKE'SMITH%'；

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

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

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

例如：

SELECTNAME

FROMEMP

WHEREEMP_NO=1234；

SELECTNAME

FROMDPT

WHEREDPTNO=10；

SELECTNAME

FROMCAT

WHERECAT.TYPE='RD'；

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

SELECTE.NAME,D.NAME,C.NAME

FROMCATC,DPTD,EMPE.DUALX

WHERENVL('X',X.DUMMY)=NVL('X',E.ROWID(+))

ANDNVL('X',X.DUMMY)=NVL('X'J).ROWID(+))

ANDNVL('X',X.DUMMY)=NVL('X',C.ROWID(+))

ANDE.EMP_NO(+)=1234

ANDD.DEPT_NO(+)=10

ANDC.CAT_TYPE(+)='RD'；

(雖然采取這種方法，效率得到提高,但是程序的可讀性大大降低，所以還是要權(quán)衡之間的利弊)

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

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

DELETEFROMEMPE

WHEREE.ROWID>(SELECTMIN(X.ROWID)

FROMEMPX

WHEREX.EMP_NO=E.EMP_NO)；

9.用TRUNCATE替代DELETE

當(dāng)刪除表中的記錄時(shí)，在通常情況下，回滾段(rollbacksegments)用來存放可以被恢復(fù)的信息.如果你

沒有COMMIT事務(wù),ORACLE會(huì)將物據(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)

10.盡量多使用COMMIT

只要有可能，在程序中盡量多使用COMMIT,這樣程序的性能得到提高，需求也會(huì)因?yàn)镃OMMIT所釋放的資源而減

少：

COMMIT所釋放的資源：

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

b.被程序語(yǔ)句獲得的鎖

c.redologbuffer中的空間

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

(在使用COMMIT時(shí)必須要注意到事務(wù)的完整性，現(xiàn)實(shí)中效率和事務(wù)完整性往往是魚和熊掌不可得兼)

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

和一般的觀點(diǎn)相反，count(*)比count(l)稍快,當(dāng)然如果可以通過索引檢索，對(duì)索引列的計(jì)數(shù)仍舊是最

快的.例如COUNT(EMPNO)

(在CSDN論壇中，曾經(jīng)對(duì)此有過相當(dāng)熱烈的討論，作者的觀點(diǎn)并不十分準(zhǔn)確，通過實(shí)際的測(cè)試,上述三種方法

并沒有顯著的性能差異)

12用Where子句替換HAVING子句

防止使用HAVING子句，HAYING只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過濾.這個(gè)處理需要排序，

總計(jì)等操作.如果能通過叫ERE子句限制記錄的數(shù)目，那就能減少這方面的開銷.

例如：

低效：

SELECTREGION,AVG(LOGSIZE)

FROMLOCATION

GROUPBYREGION

HAVINGREGIONREGION!=4SYDNEYr

ANDREGION!='PERTH'

高效

SELECTREGION,AVG(LOG_SIZE)

FROMLOCATION

WHEREREGIONREGION!='SYDNEY'

ANDREGION!='PERTH'

GROUPBYREGION

(HAVING中的條件一般用于對(duì)一些集合函數(shù)的比較，如COUNT。等等.除此而外,一般的條件應(yīng)該寫在WHERE子

句中)

Having用法：

數(shù)據(jù)表：student

表結(jié)枸；

FieldNameDataTypeLen

idint20

namevarchar25

majorvarchar25

scoreint20

sexvarchar20

rn

表數(shù)據(jù)：

編號(hào)/姓名/專業(yè)/學(xué)分/性別

idnamemajorscoresex

1jakChinese40f

2rainMath89m

3leoPhy78f

4jakMath76f

5rainChinese56m

6leoMath97f

7jakPhy45f

8jakDraw87f

9leoChinese45f

現(xiàn)在我們要得到一個(gè)視圖：

要求查詢性別為男生，并且列出每個(gè)學(xué)生的總成績(jī)：

SQL：

selects.*,sum(s.score)fromstudentswheresex=,*f''groupbys.name

Result：

idnamemajorscoresexsum(s.score)

1jakChinese40f248

3leuPhy78f220

可以看到總共查到有兩組，兩組的學(xué)生分別是jak和le。，每一組都是同一個(gè)學(xué)生，這樣我們就可以使用聚合函

數(shù)了。

只有使用了groupby語(yǔ)句，才能使用如：count。、sum。之類的聚合函數(shù)。

下面我們?cè)賹?duì)上面的結(jié)果做進(jìn)一步的篩選，只顯示總分?jǐn)?shù)大于230的學(xué)生：

SQL：

selects.*,sum(s.score)fromstudentswheresex=,'f1'groupbys.namehavingsum(s.score)>230

Result：

idnamemajorscoresexsum(s.score)

1jakChinese40f248

可見having于where的功能差不多。

結(jié)論：

1.WHERE子句用來篩選FROM子句中指定的操作所產(chǎn)生的行。

2.GROUPBY子句用來分組WHERE子句的輸出。

HAVING子句用來從分組的結(jié)果中崎詵行。

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

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

例如：

低效

SELECTTAB_NAME

FROMTABLES

WHERETAB_NAME=(SELECTTAB.NAME

FROMTAB_COLUMNS

WHEREVERSION=604)

ANDDBVER=(SELECTDBVER

FROMTAB_CO1.UMNS

WHEREVERSION=604)

高效

SELECTTAB_NAME

FROMTABLES

WHERE(TABNAME.DBVER)

=(SELECTTABNAME,DBVER)

FROMTAB_COLUMNS

WHEREVERSION=604)

Update多個(gè)Column例子:

低效：

UPDATEEMP

SETEMPCAT=(SELECTMAX(CATEGORY)FROMEMPCATEGORIES),

SALRANGE=(SELECTMAX(SALRANGE)FROMEMPCATEGORIES)

WHEREEMP.DEPT=0020；

高效：

UPDATEEMP

SET(EMP_CAT,SAL_RANGE)

=(SELECTMAX(CATEGORY),MAX(SALRANGE)

FROMEMPCATEGORIES)

WHEREEMPDEPT=0020；

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

SELECTH.EMPNO,E.ENAME,H.HIST.TYPE,T.TYPE.DESC,COUNT(*)

FROMHISTORY.TYPET.EMPE,EMP_HISTORYH

WHEREH.EMPNO=E.EMPNO

ANDH.HIST_TYPE=T.HIST_TYPE

GROUPBYH.EMPNO,E.ENAME,H.HISTTYPE.T.TYPE_DESC；

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

FUNCTIONLOOKUP_HIST_TYPE(TYPINNUMBER)RETURNVARCHAR2

AS

TDESCVARCHAR2(30)；

CURSORClIS

SELECTTYPE.DESC

FROMHISTORYTYPE

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,'T))；

END；

SELECTH.EMPNO,LOOKUP_EMP(H.EMPNO),

II.BISTTYPE,LOOKUP_HIST_TYPE(H.HIST.TYPE),COUNT(*)

FROMEMPHISTORYII

GROUPBYH.EMPNO,H.HIST_TYPE；

(經(jīng)常在論壇中看到如'能不能用一個(gè)SQL寫出…的貼子，殊不知復(fù)雜的SQL往往犧牲了執(zhí)行效率.能夠

掌握上面的運(yùn)用函數(shù)解決問題的方法在實(shí)際工作中是非常有意義的)

15.使用表的別名(Alias)

當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí)，請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上.這樣一來，就可以減少解析

的時(shí)間并減少那些由Column歧義引起的語(yǔ)法錯(cuò)誤.

(Column歧義指的是由于SQL中不同的表具有相同的Column名，當(dāng)SQL語(yǔ)句中出現(xiàn)這個(gè)Column時(shí),SQL

解析器無法判斷這個(gè)Column的歸屬)

16.用EXISTS替代IN

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

EXISTS(或MOTEXISTS)通常將提高查詢的效率.

低效：

SELECT*

FROMEMP(基礎(chǔ)表)

WHEREEMPNO>0

ANDDEPTNOIN(SELECTDEPTNO

FROMDEFT

WHERELC€='MELB')

高效：

SELECT*

FROMEMP(基礎(chǔ)表)

WHEREEMPNO>0

ANDEXISTS(SELECT'X'

FROMDEFT

WHEREDEPT.DEPTNO=EMP.DEPTNO

ANDLOC='MELB')

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

17.用NOTEXISTS替代NOTIN

在子查詢中，NOTIN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并.無論在哪種情況下,NOTIN都是最低效的(因?yàn)樗?/p>

對(duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷).為了防止使用NOTIN，我們可以把它改寫成外連接(OuterJoins)

或NOTEXISTS.

例如：

SELECT…

FROMEMP

WHEREDEPT_NONOTIN(SELECTDEPT.NO

FROMDEPT

WHEREDEPT_CAT=,A')；

為了提高效率.改寫為：

(方法一：高效)

SELECT???.

FROMEMPA,DEPTB

WHEREA.DEPT_NO=B.DEPI(+)

ANDB.DEPT_NOISNULL

ANDB.DEPT_CAT(+)='A'

(方法二：最高效)

SELECT???.

FROMEMPE

WHERENOTEXISTS(SELECT'X'

FROMDEPTD

WHERED.DEPTNO=E.DEPT_NO

ANDDEPT_CAT='A')；

18.用表連接替換EXISTS

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

SELECTENAME

FROMEMPE

WHEREEXISTS(SELECT'X'

FROMDEPT

WHEREDEPT_NO=E.DEPT_NO

ANDDEPT_CAT='A')；

(更高效)

SELECTENAME

FROMDEPTD,EMPE

WHEREE.DEPT_NO=D.I)EPT_NO

ANDDEPT_CAT='A'；

19.用EXISTS替換DISTINCT

當(dāng)提交一個(gè)包含一對(duì)多表信息(比方部門表和雇員表)的查詢時(shí),防止在SELECT子句中使用DISTINCT.一般可

以考慮用EXIST替換

例如：

低效：

SELECTDISTINCTDEPT_M),DEPT_NAME

FROMDEPTD,EMPE

WHERED.DEPT_NO=E.DEPT.NO

高效：

SELECTDEPTNO,DEPTJAME

FROMDEPTD

WHEREEXISTS(SELECT'X'

FROMEMPE

WHEREE.DEPT_NO=D.DEPT_NO)；

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

20.識(shí)別'低效執(zhí)行'的SQL語(yǔ)句

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

SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,

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

ROUND(DISK_RE?\DS/EXECliTIONS,2)Reads_per_run,

SQLTEXT

FROMVSSQLAREA

WHEREEXECUTIONS")

ANDBUFFER_GETS>0

AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8

ORDERBY4DESC；

(雖然目前各種關(guān)于SQL優(yōu)化的圖形化工具層出不窮，但是寫出自己的SQL工具來解決問題始終是一個(gè)

最好的方法)

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

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

的信息，例如解析次數(shù).執(zhí)行次數(shù),CP「使用時(shí)間等.這些數(shù)據(jù)將可以用來優(yōu)化你的系統(tǒng).

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

ALTERSESSIONSETSQL_TRACETRUE

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

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

(作者并沒有提到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è)來了解具體的配置.)

22.用索引提高效率

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

衡B-tree結(jié)構(gòu).通常，通過索引查詢數(shù)據(jù)比全表掃描要快.當(dāng)ORACLE找出執(zhí)行查詢和Update語(yǔ)句

的最正確路徑時(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>

23.索引的操作

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

索引唯一掃描(INDEXUNIQUESCAN)

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

例如：

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

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

SELECT*

FROMLODGING

WHERELODGING='ROSEHILI/；

在內(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：

SELECTLODGING

FROMLODGING

WHERELODGINGLIKE'M%'；

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

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

低一些.

例2：

SELECTLODGING

FROMLODGING

WHEREMANAGER='BILLGATES'；

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

下一步同過ROWID訪問表得到LODGING列的值.由于LODGING$MANAGER是一個(gè)非唯一性的索弓I,數(shù)

據(jù)庫(kù)不能對(duì)它執(zhí)行索引唯一掃描.

由于SQL返回LODGING列，而它并不存在于LODG1NG$MANAGER索引中，所以在索引范圍查詢后會(huì)

執(zhí)行一個(gè)通過ROWID訪問表的操作.

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

用.

SELECTLODGING

FROMLODGING

WHEREMANAGERLIKE'%HANMA\'；

在這種情況下，ORACLE將使用全表掃描.

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

基礎(chǔ)表(DrivingTable)是指被最先訪問的表(通常以全表掃描的方式被訪問).根據(jù)優(yōu)化器的

不同，SQL語(yǔ)句中基礎(chǔ)表的選擇是不一樣的.

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

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

如果你用RBO(RULEBASEDOPTIMIZER),并且所有的連接條件都有索引對(duì)應(yīng)，在這種情況下,

基礎(chǔ)表就是FROM子句中列在最后的那個(gè)表.

舉例：

SELECTA.NAME,B.MANAGER

FROMWORKERA,

LODGINGB

WHEREA.LODGING=B.LODING；

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

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

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

當(dāng)SQL語(yǔ)句的執(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

INDEXRANGESCANONDEPT_TDX

INDEXRANGESCANONCAT_IDX

26.等式比較和范圍比較

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

舉例：

DEPTNO上有一個(gè)非唯一性索引，EMP/AT也有一個(gè)非唯一性索引.

SELECTENAME

FROMEMP

WHEREDEPTNO>20

ANDEMP一CAT='A'；

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

下：

TABLEACCESSBYROWTDONEMP

INDEXRANGESCANONCAT_TDX

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

當(dāng)ORACLE無法判斷索引的等級(jí)上下差異，優(yōu)化器將只使用一個(gè)索弓I,它就是在WHERE子句中被

列在最前面的.

舉例：

DEPTNO上有一個(gè)非唯一性索弓I,EMP_CAT也有一個(gè)非唯一性索引.

SELECTENAME

FROMEMP

WHEREDEPTNO>20

ANDEMP_CAT>'A'；

這里,ORACLE只用到了DEPT_NO索引.執(zhí)行路徑如下:

TABLEACCESSBYROWIDONEMP

INDEXRANGESCANONDEPT.IDX

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

SQL>selectindex_name,uniquenessfromuser_indexeswheretable_name='EMP'；

INDEX_NAMEUNIQUENES

EMPNOUNIQUE

EMPTYPENONUNIQUE

SQL>select*fromempwhereempno>=2andemp_type='A*1*；

norowsselected

ExecutionPlan

0SELECTSTATEMENT0ptimizer=CH00SE

10TABLEACCESS(BYINDEXROWID)OF'EMP'

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

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

低！

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

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

檜索出的記錄數(shù)量少）.

舉例：

SELECTENAME

FROMEMP

WHEREEMPNO=7935

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

ANDEMP_TYPE||“=: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'

ANDEMPCLASS='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/LASS）上有幾千個(gè)重復(fù)的值.排序及合并就會(huì)成為一種不必要的負(fù)擔(dān).

在這種情況下，你希望使優(yōu)化器屏蔽掉EMP_CLASS索引.

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

SELECTENAME

FROMEMP

WHEREEMP_TYPE='A'

ANDEMP_CLASS|H；

29.防止在索引列上使用計(jì)算.

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

卷例：

低效：

SELECT…

FROMDEPT

WHERESAL*12>25000；

高效：

SELECT…

FROMDEPT

WHERESAL>25000/12；

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

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

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

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

舉例：

SELECTENAME

FROMEMP

WHEREEMPNO=2326

ANDDEPTNO=20；

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

TABLEACCESSBYROW1DONEMP

INDEXUNIQUESCANONEMP_NO_IDX

31.防止在索引列上使用NOT

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

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

舉例：

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

SELECT..?

FROMDEPT

WHEREDEPTCODENOT=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

0SELECTSTATEMENT0ptimizer=CH00SE

10TABLEACCESS(BYINDEXROWID)OF'EMP'

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

SQL>select*fromempwhereempno<=1；

norowsselected

ExecutionPlan

0SELECTSTATEMENT0ptimizer=CH00SE

10TABLEACCESS(BYINDEXROWID)OF'EMP'

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

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

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

32.用〉二替代》

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

高效：

SELECT*

FROMEMP

WHEREDEPTNO>=4

低效：

SELECT*

FROMEMP

WHEREDEPTNO>3

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

DEPTNO=3的記錄并且向前掃描到第一個(gè)DEPT大于3的記錄.

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

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

全表掃描.注意，以上規(guī)則只針對(duì)多個(gè)索引列有效.如果有column沒有被索引，查詢效率可能會(huì)

因?yàn)槟銢]有選擇OR而降低.

在下面的例子中，LOC」D和REGION上都建有索引.

高效：

SELECTLOC.ID,LOC.DESC,REGION

FROMLOCATION

WHERELOC_ID=10

UNION

SELECTLOC_ID,LOC_DESC,REGION

FROMLOCATION

WHEREREGION="MELBOURNE”

低效：

SELECTLOC_ID,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

10CONCATENATION

21TABLEACCESS(BYINDEXROWID)OF1UNIONVSOR'

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

41TABLEACCESS(BYINDEXROWID)OFfUNIONVSOR'

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

10CONCATENATION

21TABLEACCESS(BYINDEXROWID)OFfUNIONVSOR1

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

41TABLEACCESS(BYINDEXROWID)OF1UNIONVSOR,

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

Statistics

0recursivecalls

0dbblockgets

143consistentgets

0physicalreads

0redosize

63749bytessentviaSQL*Nettoclient

7751bytesreceivedviaSQL*Netfromclient

68SQL*Netroundtripsto/fromclient

0sorts(memory)

0sorts(disk)

1003rowsprocessed

SQL>select*fromunionvsor/*3rdtest*/

2wherea=1003

3union

4select*fromunionvsor

5whereb=1；

1003rowsselected.

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE

10SORT(UNIQUE)

21UNION-ALL

32TABLEACCESS(BYINDEXROWID)OF1UNIONVSOR1

43INDEX(RANGESCAN)OFrUA'(NON-UNIQUE)

52TABLEACCESS(BYINDEXROWID)OF'UNIONVSOR'

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

Statistics

0recursivecalls

0dbblockgets

10consistentgets

0physicalreads

0redosize

63735bytessentviaSQL*Nettoclient

7751bytesreceivedviaSQL*Netfromclient

68SQL*Netroundtripsto/fromclient

1sorts(memory)

0sorts(disk)

1003rowsprocessed

用UNION的效果可以從consistentgets和SQL*NET的數(shù)據(jù)交換量的減少看出

34.用IN來替換OR

下面的查詢可以被更有效率的語(yǔ)句替換：

低效：

SELECT-.

FROMLOCATION

WHERELOC_ID=10

ORLOC_ID=20

ORLOC_ID=30

高效

SELECT-

FROMLOCATION

WHERELOC_ININ（10,20,30）；

這是一條簡(jiǎn)單易記的規(guī)則，但是實(shí)際的執(zhí)行效果還須檢驗(yàn)，在0RACLE8i下，兩者的執(zhí)行路徑

似乎是相同的.

35.防止在索引列上使用ISNULL和ISNOTNULL

防止在索引中使用任何可以為空的列，ORACLE將無法使用該索引.對(duì)于單列索引，如果列包

含空值，索引中將不存在此記錄.對(duì)于復(fù)合索引，如果每個(gè)列都為空，索引中同樣不存在此記錄.

如果至少有一個(gè)列不為空，則記錄存在于索引中.

舉例：

如果唯一性索引建立在表的A列和B列上，并且表中存在一條記錄的A,B值為（123,null）,

ORACLE將不接受下一條具有相同A,B值（123,null）的記錄（插入）.然而如果

所有的索引列都為空，ORACLE將認(rèn)為整個(gè)鍵值為空而空不等于空.因此你可以插入1000

條具有相同鍵值的記錄，當(dāng)然它們都是空！

因?yàn)榭罩挡淮嬖谟谒饕兄校訵HERE子句中對(duì)索引列進(jìn)行空值比較將使ORACLE停用該索

引.

舉例：

低效：（索引失效）

SELECT…

FROMDEPARTMENT

MEREDEPT_CODEISNOTMJLL；

高效：（索引有效）

SELECT…

FROMDEPARTMENT

WHEREDEPT_CODE>=0;

36.總是使用索引的第一個(gè)列

如果索引是建立在多個(gè)列上，只有在它的第一個(gè)列Ceadingcolumn)被where子句引用時(shí)，

優(yōu)化器才會(huì)選擇使用該索引.

這也是一條簡(jiǎn)單而重要的規(guī)則.見以下實(shí)例.

SQL>createtablemultiindexusage(indanumber,indbnumber,descrvarchar2(10))；

Tablecreated.

SQL>createindexmultindexonmultiindexusage(inda,indb)；

Indexcreated.

SQL>setautotracetraceonly

SQL>select*frommultiindexusagewhereinda=1；

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE

10TABLEACCESS(BYINDEXROWID)OF'MULTIINDEXUSAGE*1

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

SQL>select*frommultiindexusagewhereindb=1；

ExecutionPlan

0SELECTSTATEMENTOptimizer=CHOOSE

10TABLEACCESS(FULL)OF*MULTIINDEXUSAGE'

很明顯，當(dāng)僅引用索引的第二個(gè)列時(shí)，優(yōu)化器使用了全表掃描而忽略了索引

37.用UNION-ALL替換UNION（如果有可能的話）

當(dāng)SQL語(yǔ)句需要UNION兩個(gè)查詢結(jié)果集合時(shí)，這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并,

然后在輸出最終結(jié)果前進(jìn)行排序.

如果用UNIONALL替代UNION,這樣排序就不是必要了.效率就會(huì)因此得到提高.

舉例：

低效:

SELECTACCT_NUM,BALANCE_AMT

FROMDEBIORANSACTIONS

WHERETRAN_DATE='31-DEC-95'

UNION

SELECTACCT.NUM,BALANCE.AMT

FROMDEBITTRANSACTIONS

WHERETRAN_DATE='31-DEC-95*

高效：

SELECTACCT_NUM,BALANCE_AMT

FROMDEBTT_TRANSACTIONS

WHERETRAN_DATE='31-DEC-95?

UNIONALL

SELECTACCTNUM,BALANCEAMT

FROMDEBITJRANSACTIONS

WHERETRAN_DATE='31-DEC-95'

需要注意的是，UNIONALL將重復(fù)輸出兩個(gè)結(jié)果集合中相同記錄,因此各位還是

要從業(yè)務(wù)需求分析使用UNIONALL的可行性.

UNION將對(duì)結(jié)果集合排序，這個(gè)操作會(huì)使用到SORT_AREA_SIZE這塊內(nèi)存.對(duì)于這

塊內(nèi)存的優(yōu)化也是相當(dāng)重要的.下面的SQL可以用來查詢排序的消耗量

Selectsubstr(name,1,25)"SortAreaName",

substr(value,1,15)nValuew

fromv$sysstat

whe