選用適合的Oracle優(yōu)化器

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

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

b.COST(基于成本)

c.CHOOSE(選擇性)

設(shè)置缺省的優(yōu)化器，可以通過對init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明，如RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS。你當(dāng)然也在SQL句級或是會話(session)級對其進(jìn)行覆蓋。

為了使用基于成本的優(yōu)化器(CBO，Cost-Based Optimizer)，你必須經(jīng)常運(yùn)行analyze命令，以增加數(shù)據(jù)庫中的對象統(tǒng)計(jì)信息(object statistics)的準(zhǔn)確性。

如果數(shù)據(jù)庫的優(yōu)化器模式設(shè)置為選擇性(CHOOSE)，那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過analyze命令有關(guān)。如果table已經(jīng)被analyze過，優(yōu)化器模式將自動成為CBO，反之，數(shù)據(jù)庫將采用RULE形式的優(yōu)化器。

在缺省情況下，Oracle采用CHOOSE優(yōu)化器，為了避免那些不必要的全表掃描(full table scan)，你必須盡量避免使用CHOOSE優(yōu)化器，而直接采用基于規(guī)則或者基于成本的優(yōu)化器。

訪問Table的方式Oracle采用兩種訪問表中記錄的方式：

a.全表掃描

全表掃描就是順序地訪問表中每條記錄。Oracle采用一次讀入多個數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描。

b. 通過ROWID訪問表

你 可以采用基于ROWID的訪問方式情況，提高訪問表的效率，ROWID包含了表中記錄的物理位置信息……Oracle采用索引(INDEX)實(shí)現(xiàn)了數(shù)據(jù)和 存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系。通常索引提供了快速訪問ROWID的方法，因此那些基于索引列的查詢就可以得到性能上的提高。

共享SQL語句

為了不重復(fù)解析相同的SQL語句，在第一次解析之后，Oracle將SQL語句存放在內(nèi)存中。這塊位于系統(tǒng)全局區(qū)域SGA(system global area)的共享池(shared buffer pool)中的內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享。因此，當(dāng)你執(zhí)行一個SQL語句(有時被稱為一個游標(biāo))時，如果它和之前的執(zhí)行過的語句完全相 同，Oracle就能很快獲得已經(jīng)被解析的語句以及最好的執(zhí)行路徑。Oracle的這個功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用。

可惜的是Oracle只對簡單的表提供高速緩沖(cache buffering) ，這個功能并不適用于多表連接查詢。

數(shù)據(jù)庫管理員必須在init.ora中為這個區(qū)域設(shè)置合適的參數(shù)，當(dāng)這個內(nèi)存區(qū)域越大，就可以保留更多的語句，當(dāng)然被共享的可能性也就越大了。

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

這里需要注明的是，Oracle對兩者采取的是一種嚴(yán)格匹配，要達(dá)成共享，SQL語句必須完全相同(包括空格，換行等)。

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

A.字符級的比較：

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

例如：

SELECT * FROM EMP;

和下列每一個都不同

SELECT * from EMP;

Select * From Emp;

SELECT * FROM EMP;

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

例如：

用戶對象名如何訪問

Jack sal_limit private synonym

Work_city public synonym

Plant_detail public synonym

Jill sal_limit private synonym

Work_city public synonym

Plant_detail table owner

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

SQL能否共享原因

select max(sal_cap) from sal_limit；不能每個用戶都有一個private synonym - sal_limit，它們是不同的對象

select count(*0 from work_city where sdesc like 'NEW%'；能兩個用戶訪問相同的對象public synonym - work_city

select a.sdesc，b.location from work_city a，plant_detail b where a.city_id = b.city_id不能用戶jack通過private synonym訪問plant_detail而jill是表的所有者，對象不同。

C.兩個SQL語句中必須使用相同的名字的綁定變量(bind variables)

例如：第一組的兩個SQL語句是相同的(可以共享)，而第二組中的兩個語句是不同的(即使在運(yùn)行時，賦于不同的綁定變量相同的值)

a.　

select pin ，name?? from people where pin = ：blk1.pin;

select pin ，name?? from people where pin = ：blk1.pin;

?

b.

select pin ，name?? from people where pin = ：blk1.ot_ind;

select pin ，name?? from people where pin = ：blk1.ov_ind;

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

Oracle的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理。在FROM子句中包含多個表的情況下，你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。當(dāng)Oracle處理多個表時，會運(yùn)用排序及合并的方式連接它們。首先，掃描第一個表(FROM子句中最后的那個表)并對記錄進(jìn)行派序，然后掃描第二個表(FROM子句中最后第二個表)，最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進(jìn)行合并。

例如：

表TAB1 16，384 條記錄

表TAB2 1條記錄

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

select count(*) from tab1，tab2 執(zhí)行時間0.96秒

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

select count(*) from tab2，tab1 執(zhí)行時間26.09秒

如果有3個以上的表連接查詢，那就需要選擇交叉表(intersection table)作為基礎(chǔ)表，交叉表是指那個被其他表所引用的表。

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

SELECT *

FROM LOCATION L ,

CATEGORY C,

EMP E

WHERE E.EMP_NO BETWEEN 1000 AND 2000

AND E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

?

將比下列SQL更有效率

SELECT *

FROM EMP E ,

LOCATION L ,

CATEGORY C

WHERE E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

AND E.EMP_NO BETWEEN 1000 AND 2000

WHERE子句中的連接順序

Oracle采用自下而上的順序解析WHERE子句，根據(jù)這個原理，表之間的連接必須寫在其他WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。

例如：

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

SELECT …

FROM EMP E

WHERE SAL > 50000

AND JOB = ‘MANAGER’

AND 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO);

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

SELECT …

FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO)

AND SAL > 50000

AND JOB = ‘MANAGER’;

SELECT子句中避免使用‘ * ’

當(dāng)你想在SELECT子句中列出所有的COLUMN時，使用動態(tài)SQL列引用‘*’是一個方便 的方法。不幸的是，這是一個非常低效的方法。實(shí)際上，Oracle在解析的過程中，會將‘*’依次轉(zhuǎn)換成所有的列名，這個工作是通過查詢數(shù)據(jù)字典完成的， 這意味著將耗費(fèi)更多的時間。

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

當(dāng)執(zhí)行每條SQL語句時，Oracle在內(nèi)部執(zhí)行了許多工作：解析SQL語句，估算索引的利用率，綁定變量，讀數(shù)據(jù)塊等等。由此可見，減少訪問數(shù)據(jù)庫的次數(shù)，就能實(shí)際上減少Oracle的工作量。

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

方法1(最低效)

SELECT EMP_NAME, SALARY, GRADE

FROM EMP

WHERE EMP_NO = 342;

SELECT EMP_NAME, SALARY, GRADE

FROM EMP

WHERE EMP_NO = 291;

方法2(次低效)

DECLARE

CURSOR C1 (E_NO NUMBER) IS

SELECT EMP_NAME,SALARY,GRADE

FROM EMP

WHERE EMP_NO = E_NO;

BEGIN

OPEN C1(342);

FETCH C1 INTO …,..,.. ;

OPEN C1(291);

FETCH C1 INTO …,..,.. ;

CLOSE C1; END;

方法3(高效)

SELECT A.EMP_NAME,A.SALARY,A.GRADE,

B.EMP_NAME, B.SALARY, B.GRADE

FROM EMP A,EMP B

WHERE A.EMP_NO = 342

AND B.EMP_NO = 291;

注意：

在SQL*Plus，SQL*Forms和Pro*C中重新設(shè)置ARRAYSIZE參數(shù)，可以增加每次數(shù)據(jù)庫訪問的檢索數(shù)據(jù)量，建議值為200。

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

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

例如：

SELECT COUNT(*)，SUM(SAL)

FROM　EMP

WHERE DEPT_NO = 0020

AND ENAME LIKE‘SMITH%’;

SELECT COUNT(*)，SUM(SAL)

FROM　EMP

WHERE DEPT_NO = 0030

AND ENAME LIKE‘SMITH%’;

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

SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL))?? D0020_COUNT,

COUNT(DECODE(DEPT_NO,0030,’X’,NULL))?? D0030_COUNT,

SUM(DECODE(DEPT_NO,0020,SAL,NULL))?? D0020_SAL,

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

FROM EMP WHERE ENAME LIKE‘SMITH%’;

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

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

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

例如：

SELECT NAME

FROM EMP

WHERE EMP_NO = 1234;

SELECT NAME

FROM DPT

WHERE DPT_NO = 10 ;

SELECT NAME

FROM CAT

WHERE CAT_TYPE =‘RD’;

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

SELECT E.NAME , D.NAME , C.NAME

FROM CAT C , DPT D , EMP E,DUAL X

WHERE NVL(‘X’,X.DUMMY) =?? NVL(‘X’,E.ROWID(+))

AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))

AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))

AND E.EMP_NO(+) = 1234

AND D.DEPT_NO(+) = 10

AND C.CAT_TYPE(+) = ‘RD’;

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

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

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

DELETE FROM EMP E

WHERE E.ROWID > (SELECT MIN(X.ROWID)

FROM EMP X

WHERE X.EMP_NO = E.EMP_NO);

11.用TRUNCATE替代DELETE

當(dāng)刪除表中的記錄時，在通常情況下，回滾段(rollback segments)用來存放可以被恢復(fù)的信息。如果你沒有COMMIT事務(wù)，Oracle會將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況)

而當(dāng)運(yùn)用TRUNCATE時，回滾段不再存放任何可被恢復(fù)的信息。當(dāng)命令運(yùn)行后，數(shù)據(jù)不能被恢復(fù)。因此很少的資源被調(diào)用，執(zhí)行時間也會很短。

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

12.盡量多使用COMMIT

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

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

b.被程序語句獲得的鎖

c.redo log buffer中的空間

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

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

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

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

(譯者按：在CSDN論壇中，曾經(jīng)對此有過相當(dāng)熱烈的討論，作者的觀點(diǎn)并不十分準(zhǔn)確，通過實(shí)際的測試，上述三種方法并沒有顯著的性能差別)

14.用Where子句替換HAVING子句

避免使用HAVING子句，HAVING 只會在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾。這個處理需要排序，總計(jì)等操作。如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷。

例如：

低效：

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION

GROUP BY REGION

HAVING REGION REGION !=‘SYDNEY’

AND REGION !=‘PERTH’

高效：

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION

WHERE REGION REGION != ‘SYDNEY’

AND REGION != ‘PERTH’

GROUP BY REGION

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

15.減少對表的查詢

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

例如：

低效

SELECT TAB_NAME

FROM TABLES

WHERE TAB_NAME = ( SELECT TAB_NAME

FROM TAB_COLUMNS

WHERE VERSION = 604)

AND DB_VER= ( SELECT DB_VER

FROM TAB_COLUMNS

WHERE VERSION = 604)

高效

SELECT TAB_NAME

FROM TABLES

WHERE (TAB_NAME,DB_VER)

= ( SELECT TAB_NAME,DB_VER)

FROM TAB_COLUMNS

WHERE VERSION = 604)

Update 多個Column 例子：

低效：

UPDATE EMP

SET EMP_CAT = (SELECT MAX(CATEGORY) FROM?? EMP_CATEGORIES),

SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM?? EMP_CATEGORIES)

WHERE EMP_DEPT = 0020;

高效：

UPDATE EMP

SET (EMP_CAT, SAL_RANGE)

= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)

FROM EMP_CATEGORIES)

WHERE EMP_DEPT = 0020;

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

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

FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H

WHERE H.EMPNO = E.EMPNO

AND H.HIST_TYPE = T.HIST_TYPE

GROUP BY?? H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;

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

FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER)?? RETURN VARCHAR2

AS

TDESC VARCHAR2(30);

CURSOR C1 IS

SELECT TYPE_DESC

FROM HISTORY_TYPE

WHERE HIST_TYPE = TYP;

BEGIN

OPEN C1;

FETCH C1 INTO TDESC;

CLOSE C1;

RETURN (NVL(TDESC,’?’));

END;

FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN?? VARCHAR2

AS

ENAME VARCHAR2(30);

CURSOR C1 IS

SELECT ENAME

FROM EMP

WHERE EMPNO=EMP;

BEGIN

OPEN C1;

FETCH C1 INTO ENAME;

CLOSE C1;

RETURN (NVL(ENAME,’?’));

END;

SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),

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

FROM EMP_HISTORY H

GROUP BY H.EMPNO , H.HIST_TYPE;

(譯者按：經(jīng)常在論壇中看到如‘能不能用一個SQL寫出…。’的貼子，殊不知復(fù)雜的SQL往往犧牲了執(zhí)行效率。能夠掌握上面的運(yùn)用函數(shù)解決問題的方法在實(shí)際工作中是非常有意義的)

17.使用表的別名(Alias)

當(dāng)在SQL語句中連接多個表時，請使用表的別名并把別名前綴于每個Column上。這樣一來，就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。

(譯者注：Column歧義指的是由于SQL中不同的表具有相同的Column名，當(dāng)SQL語句中出現(xiàn)這個Column時，SQL解析器無法判斷這個Column的歸屬)

18.用EXISTS替代IN

在許多基于基礎(chǔ)表的查詢中，為了滿足一個條件，往往需要對另一個表進(jìn)行聯(lián)接。在這種情況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。

低效：

SELECT *

FROM EMP (基礎(chǔ)表)

WHERE EMPNO > 0

AND DEPTNO IN (SELECT DEPTNO

FROM DEPT

WHERE LOC =‘MELB’)

高效：

SELECT *

FROM EMP (基礎(chǔ)表)

WHERE EMPNO > 0

AND EXISTS (SELECT‘X’

FROM DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

AND LOC =‘MELB’)

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

19.用NOT EXISTS替代NOT IN

在子查詢中，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并。無論在哪種情況下，NOT IN都是最低效的 (因?yàn)樗鼘ψ硬樵冎械谋韴?zhí)行了一個全表遍歷)。為了避免使用NOT IN，我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS。

例如：

SELECT …

FROM EMP

WHERE DEPT_NO NOT IN (SELECT DEPT_NO

FROM DEPT

WHERE DEPT_CAT=’A’);

為了提高效率。改寫為：

(方法一：高效)

SELECT ….

FROM EMP A,DEPT B

WHERE A.DEPT_NO = B.DEPT(+)

AND B.DEPT_NO IS NULL

AND B.DEPT_CAT(+) = ‘A’

(方法二：最高效)

SELECT ….

FROM EMP E

WHERE NOT EXISTS (SELECT ‘X’

FROM DEPT D

WHERE D.DEPT_NO = E.DEPT_NO

AND DEPT_CAT = ‘A’);

?

20.用表連接替換EXISTS

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

SELECT ENAME

FROM EMP E

WHERE EXISTS (SELECT ‘X’

FROM DEPT

WHERE DEPT_NO = E.DEPT_NO

AND DEPT_CAT = ‘A’);

(更高效)

SELECT ENAME

FROM DEPT D,EMP E

WHERE E.DEPT_NO = D.DEPT_NO

AND DEPT_CAT = ‘A’ ;

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

21. 用EXISTS替換DISTINCT

當(dāng)提交一個包含一對多表信息(比如部門表和雇員表)的查詢時，避免在SELECT子句中使用DISTINCT。一般可以考慮用EXIST替換

例如：

低效：

SELECT DISTINCT DEPT_NO,DEPT_NAME

FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO

高效：

SELECT DEPT_NO,DEPT_NAME

FROM DEPT D

WHERE EXISTS ( SELECT ‘X’

FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO);

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

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

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

SELECT EXECUTIONS , DISK_READS,?? BUFFER_GETS,

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

ROUND(DISK_READS/EXECUTIONS,2)?? Reads_per_run,

SQL_TEXT

FROM V$SQLAREA

WHERE EXECUTIONS>0

AND BUFFER_GETS > 0

AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS?? < 0.8

ORDER BY 4 DESC;

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

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

SQL trace工具收集正在執(zhí)行的SQL的性能狀態(tài)數(shù)據(jù)并記錄到一個跟蹤文件中。這個跟蹤文件提供了許多有用的信息，例如解析次數(shù)。執(zhí)行次數(shù)，CPU使用時間等。這些數(shù)據(jù)將可以用來優(yōu)化你的系統(tǒng)。

設(shè)置SQL TRACE在會話級別：

有效

ALTER SESSION SET SQL_TRACE TRUE

設(shè)置SQL TRACE在整個數(shù)據(jù)庫有效仿，你必須將SQL_TRACE參數(shù)在init.ora中設(shè)為TRUE，USER_DUMP_DEST參數(shù)說明了生成跟蹤文件的目錄

(譯者按：這一節(jié)中，作者并沒有提到TKPROF的用法，對SQL TRACE的用法也不夠準(zhǔn)確，設(shè)置SQL TRACE首先要在init.ora中設(shè)定TIMED_STATISTICS，這樣才能得到那些重要的時間狀態(tài)。生成的trace文件是不可讀的，所以要用TKPROF工具對其進(jìn)行轉(zhuǎn)換，TKPROF有許多執(zhí)行參數(shù)。大家可以參考Oracle手冊來了解具體的配置。)

24.用EXPLAIN PLAN 分析SQL語句

EXPLAIN PLAN是一個很好的分析SQL語句的工具，它甚至可以在不執(zhí)行SQL的情況下分析語句。通過分析，我們就可以知道Oracle是怎么樣連接表，使用什么方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱。

你需要按照從里到外，從上到下的次序解讀分析的結(jié)果。EXPLAIN PLAN分析的結(jié)果是用縮進(jìn)的格式排列的， 最內(nèi)部的操作將被最先解讀，如果兩個操作處于同一層中，帶有最小操作號的將被首先執(zhí)行。

NESTED LOOP是少數(shù)不按照上述規(guī)則處理的操作，正確的執(zhí)行路徑是檢查對NESTED LOOP提供數(shù)據(jù)的操作，其中操作號最小的將被最先處理。

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

舉例：

SQL> list

1 SELECT *

2 FROM dept, emp

3* WHERE emp.deptno = dept.deptno

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

SQL> /

14 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF 'EMP'

3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

2 db block gets

30 consistent gets

0 physical reads

0 redo size

2598 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

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

1. TABLE ACCESS (FULL) OF 'EMP'

2. INDEX (UNIQUE SCAN) OF 'PK_DEPT'?? (UNIQUE)

3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4. NESTED LOOPS (JOINING 1 AND 3)

注：目前許多第三方的工具如TOAD和Oracle本身提供的工具如OMS的SQL Analyze都提供了極其方便的EXPLAIN PLAN工具。也許喜歡圖形化界面的朋友們可以選用它們。

25.用索引提高效率

索引是表的一個概念部分，用來提高檢索數(shù)據(jù)的效率。實(shí)際上，Oracle使用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu)。通常，通過索引查詢數(shù)據(jù)比全表掃描要快。當(dāng)Oracle找出執(zhí)行查詢和Update語句的最佳路徑時，Oracle優(yōu)化器將使用索引。同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率。另一個使用索引的好處是，它提供了主鍵(primary key)的唯一性驗(yàn)證。

除了那些LONG或LONG RAW數(shù)據(jù)類型，你可以索引幾乎所有的列。通常，在大型表中使用索引特別有效。當(dāng)然，你也會發(fā)現(xiàn)，在掃描小表時，使用索引同樣能提高效率。

雖然使用索引能得到查詢效率的提高，但是我們也必須注意到它的代價(jià)。索引需要空間來存儲，也需要定期維護(hù)，每當(dāng)有記錄在表中增減或索引列被修改時，索引本身也會被修改。這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4、5次的磁盤I/O。因?yàn)樗饕枰~外的存儲空間和處理，那些不必要的索引反而會使查詢反應(yīng)時間變慢。

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

ALTER INDEXREBUILD

26.索引的操作

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

索引唯一掃描(INDEX UNIQUE SCAN)

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

例如：

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

SELECT *

FROM LODGING

WHERE LODGING = ‘ROSE HILL’;

在內(nèi)部，上述SQL將被分成兩步執(zhí)行，首先，LODGING_PK索引將通過索引唯一掃描的方式被訪問，獲得相對應(yīng)的ROWID，通過ROWID訪問表的方式執(zhí)行下一步檢索。

如果被檢索返回的列包括在INDEX列中，Oracle將不執(zhí)行第二步的處理(通過ROWID訪問表)。因?yàn)闄z索數(shù)據(jù)保存在索引中，單單訪問索引就可以完全滿足查詢結(jié)果。

下面SQL只需要INDEX UNIQUE SCAN操作。

SELECT LODGING

FROM LODGING

WHERE LODGING = ‘ROSE HILL’;

索引范圍查詢(INDEX RANGE SCAN)

適用于兩種情況：

1.基于一個范圍的檢索

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

例1：

SELECT LODGING FROM LODGING WHERE LODGING LIKE ‘M%’;

WHERE子句條件包括一系列值， Oracle將通過索引范圍查詢的方式查詢LODGING_PK . 由于索引范圍查詢將返回一組值， 它的效率就要比索引唯一掃描低一些。

例2：

SELECT LODGING

FROM LODGING

WHERE MANAGER = ‘BILL GATES’;

這個SQL的執(zhí)行分兩步，LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID)和下一步同過ROWID訪問表得到LODGING列的值。由于LODGING$MANAGER是一個非唯一性的索引，數(shù)據(jù)庫不能對它執(zhí)行索引唯一掃描。

由于SQL返回LODGING列，而它并不存在于LODGING$MANAGER索引中，所以在索引范圍查詢后會執(zhí)行一個通過ROWID訪問表的操作。

WHERE子句中，如果索引列所對應(yīng)的值的第一個字符由通配符(WILDCARD)開始，索引將不被采用。在這種情況下，Oracle將使用全表掃描。

SELECT LODGING

FROM LODGING

WHERE MANAGER LIKE‘%HANMAN’;

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

基礎(chǔ)表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問)。根據(jù)優(yōu)化器的不同，SQL語句中基礎(chǔ)表的選擇是不一樣的。

如果你使用的是CBO (COST BASED OPTIMIZER)，優(yōu)化器會檢查SQL語句中的每個表的物理大小，索引的狀態(tài)，然后選用花費(fèi)最低的執(zhí)行路徑。

如果你用RBO (RULE BASED OPTIMIZER)，并且所有的連接條件都有索引對應(yīng)，在這種情況下，基礎(chǔ)表就是FROM 子句中列在最后的那個表。blog

舉例：

SELECT A.NAME ，?? B.MANAGER

FROM　WORKER A，

LODGING B

WHERE　A.LODGING =?? B.LODING;

由于LODGING表的LODING列上有一個索引，而且WORKER表中沒有相比較的索引，WORKER表將被作為查詢中的基礎(chǔ)表。

28.多個平等的索引

當(dāng)SQL語句的執(zhí)行路徑可以使用分布在多個表上的多個索引時，Oracle會同時使用多個索引并在運(yùn)行時對它們的記錄進(jìn)行合并， 檢索出僅對全部索引有效的記錄。

在Oracle選擇執(zhí)行路徑時，唯一性索引的等級高于非唯一性索引。然而這個規(guī)則只有當(dāng)WHERE子句中索引列和常量比較才有效。如果索引列和其他表的索引類相比較。這種子句在優(yōu)化器中的等級是非常低的。

如果不同表中兩個想同等級的索引將被引用，FROM子句中表的順序?qū)Q定哪個會被率先使用。FROM子句中最后的表的索引將有最高的優(yōu)先級。

如果相同表中兩個想同等級的索引將被引用，WHERE子句中最先被引用的索引將有最高的優(yōu)先級。

舉例：

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

SELECT ENAME，

FROM EMP

WHERE DEPT_NO = 20

AND EMP_CAT = ‘A’;

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

TABLE ACCESS BY ROWID ON EMP

AND-EQUAL

INDEX RANGE SCAN ON DEPT_IDX

INDEX RANGE SCAN ON CAT_IDX

29.等式比較和范圍比較

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

舉例：

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

SELECT ENAME

FROM EMP

WHERE DEPTNO > 20

AND EMP_CAT = ‘A’;

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

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON CAT_IDX

30.不明確的索引等級

當(dāng)Oracle無法判斷索引的等級高低差別，優(yōu)化器將只使用一個索引，它就是在WHERE子句中被列在最前面的。

舉例：

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

SELECT ENAME

FROM EMP

WHERE DEPTNO > 20

AND EMP_CAT > ‘A’;

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

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON DEPT_IDX

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

SQL> select index_name，?? uniqueness from user_indexes where table_name = 'EMP';

INDEX_NAME UNIQUENES

------------------------------ ---------

EMPNO UNIQUE

EMPTYPE NONUNIQUE

SQL> select * from emp where empno >=?? 2 and emp_type = 'A' ;

no rows selected

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (RANGE SCAN) OF 'EMPTYPE'?? (NON-UNIQUE)

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

?