1、oracle數(shù)據(jù)庫性能優(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-Based Optimizer)，你必須經(jīng)常運(yùn)行analyze命令，以增加數(shù)據(jù)庫中的對(duì)象統(tǒng)計(jì)信息(object statistics)的準(zhǔn)確性。如果數(shù)據(jù)庫的優(yōu)化器

2、模式設(shè)置為選擇性(CHOOSE)，那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過analyze命令有關(guān)。如果table已經(jīng)被analyze過，優(yōu)化器模式將自動(dòng)成為CBO，反之，數(shù)據(jù)庫將采用RULE形式的優(yōu)化器。在缺省情況下，Oracle采用CHOOSE優(yōu)化器，為了避免那些不必要的全表掃描(full table scan)，你必須盡量避免使用CHOOSE優(yōu)化器，而直接采用基于規(guī)則或者基于成本的優(yōu)化器。2.訪問Table的方式Oracle采用兩種訪問表中記錄的方式：a.全表掃描全表掃描就是順序地訪問表中每條記錄。Oracle采用一次讀入多個(gè)數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描。b. 通過R

3、OWID訪問表你可以采用基于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(system global area)的共享池(shared buffer pool)中的內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享。因此，當(dāng)你執(zhí)行一個(gè)SQL語句(有時(shí)被稱為一個(gè)游標(biāo))時(shí)，如

4、果它和之前的執(zhí)行過的語句完全相同，Oracle就能很快獲得已經(jīng)被解析的語句以及最好的執(zhí)行路徑。Oracle的這個(gè)功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用。可惜的是Oracle只對(duì)簡(jiǎn)單的表提供高速緩沖(cache buffering) ，這個(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語句必須完全相同(包括空格，

5、換行等)。共享的語句必須滿足三個(gè)條件：A.字符級(jí)的比較：當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同。例如：SELECT * FROM EMP;和下列每一個(gè)都不同SELECT * from EMP; Select * From Emp; SELECT * FROM EMP; B.兩個(gè)語句所指的對(duì)象必須完全相同：例如：用戶對(duì)象名如何訪問Jack sal_limit private synonym Work_city public

6、 synonym Plant_detail public synonym Jill sal_limit private synonym Work_city public synonym Plant_detail table owner 考慮一下下列SQL語句能否在這兩個(gè)用戶之間共享。SQL能否共享原因select max(sal_cap) from sal_limit；不能每個(gè)用戶都有一個(gè)private synonym - sal_limit，它

7、們是不同的對(duì)象select count(*0 from work_city where sdesc like 'NEW%'；能兩個(gè)用戶訪問相同的對(duì)象public synonym - work_cityselect a.sdesc，b.location from work_city a，plant_detail b where a.city_id = b.city_id不能用戶jack通過private synonym訪問plant_detail而jill是表的所有者，對(duì)象不同。C.兩個(gè)SQL語句中必須使用相同的名字的綁定變量(bind variables)例如：第一組的兩個(gè)SQ

8、L語句是相同的(可以共享)，而第二組中的兩個(gè)語句是不同的(即使在運(yùn)行時(shí)，賦于不同的綁定變量相同的值)a. select pin ，name from people where pin = ：blk1.pin; select pin ，name from people where pin = ：blk1.pin;  b. select pin ，name fr

9、om people where pin = ：blk1.ot_ind; select pin ，name from people where pin = ：blk1.ov_ind; 4.選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)Oracle的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理。在FROM子句中包含多個(gè)表的情況下，你必須選擇記錄條數(shù)最少的表作為

10、基礎(chǔ)表。當(dāng)Oracle處理多個(gè)表時(shí)，會(huì)運(yùn)用排序及合并的方式連接它們。首先，掃描第一個(gè)表(FROM子句中最后的那個(gè)表)并對(duì)記錄進(jìn)行派序，然后掃描第二個(gè)表(FROM子句中最后第二個(gè)表)，最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并。例如：表TAB1 16，384 條記錄表TAB2 1條記錄選擇TAB2作為基礎(chǔ)表(最好的方法)select count(*) from tab1，tab2 執(zhí)行時(shí)間0.96秒選擇TAB2作為基礎(chǔ)表(不佳的方法)select count(*) from tab2，tab1 執(zhí)行時(shí)間26.09秒如果有3個(gè)以上的表連接查詢，那就需要選擇交叉表(interse

11、ction table)作為基礎(chǔ)表，交叉表是指那個(gè)被其他表所引用的表。例如：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 =

12、0;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 20005. WHERE子句中的連接順序Oracle采用自下而上的

13、順序解析WHERE子句，根據(jù)這個(gè)原理，表之間的連接必須寫在其他WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。例如：(低效，執(zhí)行時(shí)間156.3秒）SELECT  FROM EMP E WHERE SAL > 50000 AND JOB = MANAGER AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR

14、=E.EMPNO); (高效，執(zhí)行時(shí)間10.6秒)SELECT  FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = MANAGER; 6.SELECT子句中避免使用 * 當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí)，

15、使用動(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(最低效)SELECT EMP_NAME， SALARY， GRADE FROM&

16、#160;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

17、 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.GRAD

18、E 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。8.使用DECODE函數(shù)來減少處理時(shí)間使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表。例如：SELECT COUNT(*)，SUM(SAL) FROMEMP WH

19、ERE DEPT_NO = 0020 AND ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKESMITH%; 你可以用DECODE函數(shù)高效地得到相同結(jié)果SELECT COUNT(DECODE(DEPT_NO，0020，X，NULL) D0020_COUNT， COUNT(DE

20、CODE(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 LIKESMITH%; 類似的，DECODE函數(shù)也可以運(yùn)用于GROUP BY 和ORDER BY子句中。9.整合簡(jiǎn)單，無關(guān)聯(lián)的數(shù)據(jù)庫訪問如果你有幾個(gè)簡(jiǎn)單的數(shù)據(jù)庫查詢語句，你可以把它們整合到一個(gè)

21、查詢中(即使它們之間沒有關(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個(gè)查詢可以被合并成一個(gè)：SELECT E.NAME 

22、;， 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(+) A

23、ND E.EMP_NO(+) = 1234 AND D.DEPT_NO(+) = 10 AND C.CAT_TYPE(+) = RD; 10.刪除重復(fù)記錄最高效的刪除重復(fù)記錄方法(因?yàn)槭褂昧薘OWID)DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE 

24、;X.EMP_NO = E.EMP_NO); 11.用TRUNCATE替代DELETE當(dāng)刪除表中的記錄時(shí)，在通常情況下，回滾段(rollback segments)用來存放可以被恢復(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只要有可能，在程序中盡量多使用

25、COMMIT，這樣程序的性能得到提高，需求也會(huì)因?yàn)镃OMMIT所釋放的資源而減少：COMMIT所釋放的資源：a.回滾段上用于恢復(fù)數(shù)據(jù)的信息。b.被程序語句獲得的鎖c.redo log buffer中的空間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í)

26、際的測(cè)試，上述三種方法并沒有顯著的性能差別)14.用Where子句替換HAVING子句避免使用HAVING子句，HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過濾。這個(gè)處理需要排序，總計(jì)等操作。如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷。例如：低效：SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION !=SYDNEY AND REGION

27、0;!=PERTH 高效：SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != SYDNEY AND REGION != PERTH GROUP BY REGION15.減少對(duì)表的查詢?cè)诤凶硬樵兊腟QL語句中，要特別注意減少對(duì)表的查詢。例如：低效SELECT TAB_NAME FROM TABLES WHERE 

28、;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&

29、#160;(TAB_NAME，DB_VER) = ( SELECT TAB_NAME，DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) Update 多個(gè)Column 例子：低效：UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES)， SAL_RANGE&

30、#160;= (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&

31、#160;= 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

32、.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; BEGI

33、N 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 F

34、ROM 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_HISTOR

35、Y H GROUP BY H.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

36、時(shí)，SQL解析器無法判斷這個(gè)Column的歸屬)18.用EXISTS替代IN在許多基于基礎(chǔ)表的查詢中，為了滿足一個(gè)條件，往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接。在這種情況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。低效：SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC =M

37、ELB) 高效：SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO > 0 AND EXISTS (SELECTX FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC =MELB) (譯者按：相對(duì)來說，用NOT EXISTS替換NOT IN將更顯著地提高效率，下一節(jié)中將指出)19.用NOT EXISTS替代

38、NOT IN在子查詢中，NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并。無論在哪種情況下，NOT IN都是最低效的 (因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷)。為了避免使用NOT IN，我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS。例如：SELECT  FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=A); 為了提高效率。改寫為：(方法一：

39、高效)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

40、0;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

41、60;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)提交一個(gè)包含一對(duì)多表信息(比如部門表和雇員表)的查詢時(shí)，避免在SEL

42、ECT子句中使用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&

43、#160;E WHERE E.DEPT_NO = D.DEPT_NO); EXISTS使查詢更為迅速，因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回結(jié)果。22.識(shí)別低效執(zhí)行的SQL語句用下列SQL工具找出低效SQL：SELECT EXECUTIONS ， DISK_READS， BUFFER_GETS， ROUND(BUFFER_GETS-DISK_READS)/BUFFER_GETS，2) Hit_radio， ROUND(DISK_READS/EXECUTIO

44、NS，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工具來解決問題始終

45、是一個(gè)最好的方法)23.使用TKPROF工具來查詢SQL性能狀態(tài)SQL trace工具收集正在執(zhí)行的SQL的性能狀態(tài)數(shù)據(jù)并記錄到一個(gè)跟蹤文件中。這個(gè)跟蹤文件提供了許多有用的信息，例如解析次數(shù)。執(zhí)行次數(shù)，CPU使用時(shí)間等。這些數(shù)據(jù)將可以用來優(yōu)化你的系統(tǒng)。設(shè)置SQL TRACE在會(huì)話級(jí)別：有效ALTER SESSION SET SQL_TRACE TRUE設(shè)置SQL TRACE在整個(gè)數(shù)據(jù)庫有效仿，你必須將SQL_TRACE參數(shù)在init.ora中設(shè)為TRUE，USER_DUMP_DEST參數(shù)說明了生成跟蹤文件的目錄(譯者按：這一節(jié)中，作者并沒有提到TKPRO

46、F的用法，對(duì)SQL TRACE的用法也不夠準(zhǔn)確，設(shè)置SQL TRACE首先要在init.ora中設(shè)定TIMED_STATISTICS，這樣才能得到那些重要的時(shí)間狀態(tài)。生成的trace文件是不可讀的，所以要用TKPROF工具對(duì)其進(jìn)行轉(zhuǎn)換，TKPROF有許多執(zhí)行參數(shù)。大家可以參考Oracle手冊(cè)來了解具體的配置。)24.用EXPLAIN PLAN 分析SQL語句EXPLAIN PLAN是一個(gè)很好的分析SQL語句的工具，它甚至可以在不執(zhí)行SQL的情況下分析語句。通過分析，我們就可以知道Oracle是怎么樣連接表，使用什么方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱。你需要按照從里到外，從上到

47、下的次序解讀分析的結(jié)果。EXPLAIN PLAN分析的結(jié)果是用縮進(jìn)的格式排列的， 最內(nèi)部的操作將被最先解讀，如果兩個(gè)操作處于同一層中，帶有最小操作號(hào)的將被首先執(zhí)行。NESTED LOOP是少數(shù)不按照上述規(guī)則處理的操作，正確的執(zhí)行路徑是檢查對(duì)NESTED LOOP提供數(shù)據(jù)的操作，其中操作號(hào)最小的將被最先處理。譯者按：通過實(shí)踐，感到還是用SQLPLUS中的SET TRACE功能比較方便。舉例：SQL> list 1 SELECT * 2 FROM dept， emp 3* WHERE 

48、;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&#

49、160;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 

50、;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 clien

51、t 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&#

52、160;'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.用索引提高

53、效率索引是表的一個(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è)使用索引的好處是，它提供了主鍵(primary key)的唯一性驗(yàn)證。除了那些LONG或LONG RAW數(shù)據(jù)類型，你可以索引幾乎所有的列。通常，在大型表中使用索引特別有效。當(dāng)然，你也會(huì)發(fā)現(xiàn)，在掃描小表時(shí)，使用索引同樣能提高效率。雖然使用索引能得到查詢效率的提高，但是我們也必須注意到它的代價(jià)。索引需要空間來存儲(chǔ)，也

54、需要定期維護(hù)，每當(dāng)有記錄在表中增減或索引列被修改時(shí)，索引本身也會(huì)被修改。這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4、5次的磁盤I/O。因?yàn)樗饕枰~外的存儲(chǔ)空間和處理，那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢。譯者按：定期的重構(gòu)索引是有必要的。ALTER INDEXREBUILD26.索引的操作Oracle對(duì)索引有兩種訪問模式。索引唯一掃描(INDEX UNIQUE SCAN)大多數(shù)情況下，優(yōu)化器通過WHERE子句訪問INDEX.例如：表LODGING有兩個(gè)索引：建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LOD

55、GING$MANAGER.SELECT * FROM LODGING WHERE LODGING = ROSE HILL; 在內(nèi)部，上述SQL將被分成兩步執(zhí)行，首先，LODGING_PK索引將通過索引唯一掃描的方式被訪問，獲得相對(duì)應(yīng)的ROWID，通過ROWID訪問表的方式執(zhí)行下一步檢索。如果被檢索返回的列包括在INDEX列中，Oracle將不執(zhí)行第二步的處理(通過ROWID訪問表)。因?yàn)闄z索數(shù)據(jù)保存在索引中，單單訪問索引就可以完全滿足查詢結(jié)果。下面SQL只需要INDEX UNIQUE SCAN操作。SEL

56、ECT LODGING FROM LODGING WHERE LODGING = ROSE HILL; 索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況：1.基于一個(gè)范圍的檢索2.基于非唯一性索引的檢索例1：SELECT LODGING FROM LODGING WHERE LODGING LIKE M%;WHERE子句條件包括一系列值， Oracle將通過索引范圍查詢的方式查詢LODGING_PK . 由于索引范圍查詢將

57、返回一組值， 它的效率就要比索引唯一掃描低一些。例2：SELECT LODGING FROM LODGING WHERE MANAGER = BILL GATES; 這個(gè)SQL的執(zhí)行分兩步，LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID)和下一步同過ROWID訪問表得到LODGING列的值。由于LODGING$MANAGER是一個(gè)非唯一性的索引，數(shù)據(jù)庫不能對(duì)它執(zhí)行索引唯一掃描。由于SQL返回LODGING列，而它并不存在于LODGING$MANAGER索引中，所以在索引范圍

58、查詢后會(huì)執(zhí)行一個(gè)通過ROWID訪問表的操作。WHERE子句中，如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通配符(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)化器會(huì)

59、檢查SQL語句中的每個(gè)表的物理大小，索引的狀態(tài)，然后選用花費(fèi)最低的執(zhí)行路徑。如果你用RBO (RULE BASED OPTIMIZER)，并且所有的連接條件都有索引對(duì)應(yīng)，在這種情況下，基礎(chǔ)表就是FROM 子句中列在最后的那個(gè)表。blog舉例：SELECT A.NAME ， B.MANAGERFROMWORKER A，LODGING BWHEREA.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í)是非常低的。如果不同表中