Oracle SQL語(yǔ)句優(yōu)化規(guī)則

1、Oracle語(yǔ)句優(yōu)化規(guī)則整理目 錄1. 選用適合的ORACLE優(yōu)化器 - 32. 訪問Table的方式ORACLE 采用兩種訪問表中記錄的方式 - 33. 共享SQL語(yǔ)句 - 34. 選擇最有效率的表名順序（只在基于規(guī)則的優(yōu)化器中有效） - 55. WHERE子句中的連接順序。 - 66. SELECT子句中避免使用 * - 67. 減少訪問數(shù)據(jù)庫(kù)的次數(shù) - 78. 使用DECODE函數(shù)來減少處理時(shí)間 - 89. 整合簡(jiǎn)單，無關(guān)聯(lián)的數(shù)據(jù)庫(kù)訪問 - 810. 刪除重復(fù)記錄 - 911. 用TRUNCATE替代DELETE - 912. 盡量多使用COMMIT - 913. 計(jì)算記錄條數(shù) - 10

2、14. 用Where子句替換HAVING子句 - 1015. 減少對(duì)表的查詢 - 1116. 通過內(nèi)部函數(shù)提高SQL效率。 - 1217. 使用表的別名（Alias） - 1318. 用EXISTS替代IN - 1319. 用NOT EXISTS替代NOT IN - 1320. 用表連接替換EXISTS - 1421. 用EXISTS替換DISTINCT - 1522. 使用TKPROF 工具來查詢SQL性能狀態(tài) - 1523. 用EXPLAIN PLAN 分析SQL語(yǔ)句 - 1624. 用索引提高效率 - 1725. 索引的操作 - 1826. 基礎(chǔ)表的選擇 - 1927. 多個(gè)平等的索引

3、- 2028. 等式比較和范圍比較 - 2029. 不明確的索引等級(jí) - 2130. 強(qiáng)制索引失效 - 2231. 避免在索引列上使用計(jì)算。 - 2332. 自動(dòng)選擇索引 - 2333. 避免在索引列上使用NOT - 2434. 用=替代 - 2535. 用UNION替換OR （適用于索引列） - 2636. 用IN來替換OR - 2937. 避免在索引列上使用IS NULL和IS NOT NULL - 2938. 總是使用索引的第一個(gè)列 - 3039. ORACLE內(nèi)部操作 - 3140. UNION-ALL 替換UNION （ 如果有可能的話） - 3141. 42. 43. 44. 45

4、. 46. 47. 48. 49. 50. 51.52. 使用提示（Hints）- 32 用WHERE替代ORDER BY - 33 避免改變索引列的類型。 - 34 需要當(dāng)心的WHERE子句 - 35 連接多個(gè)掃描 - 37 CBO下使用更具選擇性的索引 - 38 避免使用耗費(fèi)資源的操作 - 38 優(yōu)化GROUP BY - 38 使用日期 - 39 使用顯式的游標(biāo)（CURSORs） - 40 優(yōu)化EXPORT和IMPORT - 40 分離表和索引 - 401. 選用適合的ORACLE優(yōu)化器ORACLE的優(yōu)化器共有3種：a. RULE （基于規(guī)則）b. COST （基于成本）c. CHOOSE

5、 （選擇性）設(shè)置缺省的優(yōu)化器，可以通過對(duì)init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明，如RULE，COST，CHOOSE，ALL_ROWS，F(xiàn)IRST_ROWS . 你當(dāng)然也在SQL句級(jí)或是會(huì)話（session）級(jí)對(duì)其進(jìn)行覆蓋。為了使用基于成本的優(yōu)化器（CBO， Cost-Based Optimizer） ， 你必須經(jīng)常運(yùn)行analyze 命令，以增加數(shù)據(jù)庫(kù)中的對(duì)象統(tǒng)計(jì)信息（object statistics）的準(zhǔn)確性。如果數(shù)據(jù)庫(kù)的優(yōu)化器模式設(shè)置為選擇性（CHOOSE），那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過analyze命令有關(guān)。 如果table已經(jīng)被analyze過， 優(yōu)化

6、器模式將自動(dòng)成為CBO ， 反之，數(shù)據(jù)庫(kù)將采用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. 通過ROWID訪問表你可以采用基于ROWID的訪問方式情況，提高訪問表的效率， ROWID包含了表中記錄的物理位置信息ORAC

7、LE采用索引（INDEX）實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置（ROWID）之間的聯(lián)系。 通常索引提供了快速訪問ROWID的方法，因此那些基于索引列的查詢就可以得到性能上的提高。3. 共享SQL語(yǔ)句為了不重復(fù)解析相同的SQL語(yǔ)句，在第一次解析之后， ORACLE將SQL語(yǔ)句存放在內(nèi)存中。這塊位于系統(tǒng)全局區(qū)域SGA（system global area）的共享池（shared buffer pool）中的內(nèi)存可以被所有的數(shù)據(jù)庫(kù)用戶共享。 因此，當(dāng)你執(zhí)行一個(gè)SQL語(yǔ)句（有時(shí)被稱為一個(gè)游標(biāo)）時(shí)，如果它和之前的執(zhí)行過的語(yǔ)句完全相同， ORACLE就能很快獲得已經(jīng)被解析的語(yǔ)句以及最好的執(zhí)行路徑。 ORACLE

8、的這個(gè)功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用?？上У氖荗RACLE只對(duì)簡(jiǎn)單的表提供高速緩沖（cache buffering） ，這個(gè)功能并不適用于多表連接查詢。數(shù)據(jù)庫(kù)管理員必須在init.ora中為這個(gè)區(qū)域設(shè)置合適的參數(shù)，當(dāng)這個(gè)內(nèi)存區(qū)域越大，就可以保留更多的語(yǔ)句，當(dāng)然被共享的可能性也就越大了。當(dāng)你向ORACLE 提交一個(gè)SQL語(yǔ)句，ORACLE會(huì)首先在這塊內(nèi)存中查找相同的語(yǔ)句。這里需要注明的是，ORACLE對(duì)兩者采取的是一種嚴(yán)格匹配，要達(dá)成共享，SQL語(yǔ)句必須完全相同（包括空格，換行等）。共享的語(yǔ)句必須滿足三個(gè)條件：A. 字符級(jí)的比較：當(dāng)前被執(zhí)行的語(yǔ)句和共享池中的語(yǔ)句必須完全相同。

9、例如：SELECT * FROM EMP；和下列每一個(gè)都不同SELECT * from EMP；Select * From Emp；SELECT * FROM EMP；B. 兩個(gè)語(yǔ)句所指的對(duì)象必須完全相同：例如：用戶 對(duì)象名 如何訪問Jack sal_limit private synonymWork_city public synonymPlant_detail public synonymJill sal_limit private synonymWork_city public synonymPlant_detail table owner考慮一下下列SQL語(yǔ)句能否在這兩個(gè)用戶之間共享。

10、SQL 能否共享原因select max(sal_cap) from sal_limit; 不能 每個(gè)用戶都有一個(gè)private synonym - sal_limit , 它們是不同的對(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 syno

11、nym訪問plant_detail 而jill 是表的所有者,對(duì)象不同.C. 兩個(gè)SQL語(yǔ)句中必須使用相同的名字的綁定變量（bind variables）例如：第一組的兩個(gè)SQL語(yǔ)句是相同的（可以共享），而第二組中的兩個(gè)語(yǔ)句是不同的（即使在運(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 from people where pin = ：blk1.ot_ind

12、；select pin ， name from people where pin = ：blk1.ov_ind；4. 選擇最有效率的表名順序（只在基于規(guī)則的優(yōu)化器中有效）ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最后的表（基礎(chǔ)表 driving table）將被最先處理。 在FROM子句中包含多個(gè)表的情況下，你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。當(dāng)ORACLE處理多個(gè)表時(shí)， 會(huì)運(yùn)用排序及合并的方式連接它們。首先，掃描第一個(gè)表（FROM子句中最后的那個(gè)表）并對(duì)記錄進(jìn)行派序，然后掃描第二個(gè)表（FROM子句中最后第二個(gè)表），最后將所有從第二個(gè)表中檢索出的記

13、錄與第一個(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è)以上的表連接查詢， 那就需要選擇交叉表（intersection table）作為基礎(chǔ)表， 交叉表是指那個(gè)被其他表所引用的表。例如： EMP表描述了LOCATION表和CATEGORY表的交集。SELECT *FROM LOCATION L ,CATEGO

14、RY C,EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCN將比下列SQL更有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 20005. WHERE子句中的連接順序。ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個(gè)原理，表之間的連接必須寫在其他WHERE條件之前， 那些可以過濾掉最大

15、數(shù)量記錄的條件必須寫在WHERE子句的末尾。例如：（低效，執(zhí)行時(shí)間156.3秒）SELECT FROM EMP EWHERE SAL 50000AND JOB = MANAGERAND 25 (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO);(高效,執(zhí)行時(shí)間10.6秒)SELECT FROM EMP EWHERE 25 50000AND JOB = MANAGER;6. SELECT子句中避免使用 * 當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí)，使用動(dòng)態(tài)SQL列引用 * 是一個(gè)方便的方法。不幸的是，這是一個(gè)非常低效的方法。 實(shí)際上，ORACLE在解

16、析的過程中， 會(huì)將* 依次轉(zhuǎn)換成所有的列名， 這個(gè)工作是通過查詢數(shù)據(jù)字典完成的， 這意味著將耗費(fèi)更多的時(shí)間。7. 減少訪問數(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 （最低效）SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 342;SELECT EMP_NAME , SALARY , GRA

17、DEFROM EMPWHERE EMP_NO = 291;方法2 （次低效）DECLARECURSOR C1 (E_NO NUMBER) ISSELECT EMP_NAME,SALARY,GRADEFROM EMPWHERE EMP_NO = E_NO;BEGINOPEN 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.GRADEFROM EMP A,

18、EMP BWHERE A.EMP_NO = 342AND B.EMP_NO = 291;注意：在SQL*Plus ， SQL*Forms和Pro*C中重新設(shè)置ARRAYSIZE參數(shù)， 可以增加每次數(shù)據(jù)庫(kù)訪問的檢索數(shù)據(jù)量 ，建議值為200.8. 使用DECODE函數(shù)來減少處理時(shí)間使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表。例如：SELECT COUNT(*)，SUM(SAL)FROM EMPWHERE DEPT_NO = 0020AND ENAME LIKE SMITH%;SELECT COUNT(*)，SUM(SAL)FROM EMPWHERE DEPT_NO = 0030

19、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_SALFROM EMP WHERE ENAME LIKE SMITH%;類似的，DECODE函數(shù)也可以運(yùn)用于GROUP BY 和ORDER BY子句

20、中。9. 整合簡(jiǎn)單，無關(guān)聯(lián)的數(shù)據(jù)庫(kù)訪問如果你有幾個(gè)簡(jiǎn)單的數(shù)據(jù)庫(kù)查詢語(yǔ)句，你可以把它們整合到一個(gè)查詢中（即使它們之間沒有關(guān)系）例如：SELECT NAMEFROM EMPWHERE EMP_NO = 1234;SELECT NAMEFROM DPTWHERE DPT_NO = 10 ;SELECT NAMEFROM CATWHERE CAT_TYPE = RD;上面的3個(gè)查詢可以被合并成一個(gè)：SELECT E.NAME , D.NAME , C.NAMEFROM CAT C , DPT D , EMP E,DUAL XWHERE NVL(X,X.DUMMY) = NVL(X,E.ROWID(+)

21、AND NVL(X,X.DUMMY) = NVL(X,D.ROWID(+)AND NVL(X,X.DUMMY) = NVL(X,C.ROWID(+)AND E.EMP_NO(+) = 1234AND D.DEPT_NO(+) = 10AND C.CAT_TYPE(+) = RD;（譯者按： 雖然采取這種方法，效率得到提高，但是程序的可讀性大大降低，所以讀者還是要權(quán)衡之間的利弊）10. 刪除重復(fù)記錄最高效的刪除重復(fù)記錄方法 （ 因?yàn)槭褂昧薘OWID）DELETE FROM EMP EWHERE E.ROWID (SELECT MIN(X.ROWID)FROM EMP XWHERE X.EMP_N

22、O = 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只要有可能，在程序中盡量多使用COMMIT， 這樣程序的性能得

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

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

25、EGION != SYDNEYAND REGION != PERTHGROUP BY REGION（譯者按： HAVING 中的條件一般用于對(duì)一些集合函數(shù)的比較，如COUNT（） 等等。 除此而外，一般的條件應(yīng)該寫在WHERE子句中）15. 減少對(duì)表的查詢?cè)诤凶硬樵兊腟QL語(yǔ)句中，要特別注意減少對(duì)表的查詢。例如：低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)AND DB_VER= ( SELECT DB_VERFROM TAB_COLUMNSW

26、HERE VERSION = 604)高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNSWHERE VERSION = 604)Update 多個(gè)Column 例子：低效：UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 002

27、0;高效：UPDATE EMPSET (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 HWHERE H.EMPNO = E.EMPNOAND H.HIST_TYPE = T.HIST_TYPEGROUP BY

28、H.EMPNO, E.ENAME, H.HIST_TYPE, T.TYPE_DESC;通過調(diào)用下面的函數(shù)可以提高效率。FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2ASTDESC VARCHAR2(30);CURSOR C1 ISSELECT TYPE_DESCFROM HISTORY_TYPEWHERE HIST_TYPE = TYP;BEGINOPEN C1;FETCH C1 INTO TDESC;CLOSE C1;RETURN (NVL(TDESC,?);END;FUNCTION LOOKUP_EMP(EMP IN NUM

29、BER) RETURN VARCHAR2ASENAME VARCHAR2(30);CURSOR C1 ISSELECT ENAMEFROM EMPWHERE EMPNO=EMP;BEGINOPEN 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 HGROUP BY H.EMPNO , H.HIST_TYPE;（譯者按： 經(jīng)常在論

30、壇中看到如 能不能用一個(gè)SQL寫出。 的貼子， 殊不知復(fù)雜的SQL往往犧牲了執(zhí)行效率。 能夠掌握上面的運(yùn)用函數(shù)解決問題的方法在實(shí)際工作中是非常有意義的）17. 使用表的別名（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的歸屬）18. 用EXISTS替代IN在許多基于基礎(chǔ)表的查詢中，為了滿足一個(gè)條件，往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)

31、接。在這種情況下， 使用EXISTS（或NOT EXISTS）通常將提高查詢的效率。低效：SELECT *FROM EMP (基礎(chǔ)表)WHERE EMPNO 0AND DEPTNO IN (SELECT DEPTNOFROM DEPTWHERE LOC = MELB)高效：SELECT *FROM EMP (基礎(chǔ)表)WHERE EMPNO 0AND EXISTS (SELECT XFROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = MELB)（譯者按： 相對(duì)來說，用NOT EXISTS替換NOT IN 將更顯著地提高效率，下一節(jié)中將指出）19. 用

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

33、ND B.DEPT_NO IS NULLAND B.DEPT_CAT(+) = A（方法二： 最高效）SELECT .FROM EMP EWHERE NOT EXISTS (SELECT XFROM DEPT DWHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = A);20. 用表連接替換EXISTS通常來說 ， 采用表連接的方式比EXISTS更有效率SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT XFROM DEPTWHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = A); （更高效）SEL

34、ECT ENAMEFROM DEPT D, EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT = A;（譯者按： 在RBO的情況下，前者的執(zhí)行路徑包括FILTER，后者使用NESTED LOOP）21. 用EXISTS替換DISTINCT當(dāng)提交一個(gè)包含一對(duì)多表信息（比如部門表和雇員表）的查詢時(shí)，避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換例如：低效：SELECT DISTINCT DEPT_NO, DEPT_NAMEFROM DEPT D, EMP EWHERE D.DEPT_NO = E.DEPT_NO高效：SELECT

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

36、ads_per_run,SQL_TEXTFROM V$SQLAREAWHERE EXECUTIONS0AND BUFFER_GETS 0AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS list1 SELECT *2 FROM dept, emp3* WHERE emp.deptno = dept.deptnoSQL set autotrace traceonly /*traceonly 可以不顯示執(zhí)行結(jié)果*/SQL /14 rows selected.Execution Plan-0 SELECT STATEMENT Optimizer=CHOOSE1 0 N

37、ESTED LOOPS2 1 TABLE ACCESS (FULL) OF EMP3 1 TABLE ACCESS (BY INDEX ROWID) OF DEPT4 3 INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)Statistics-0 recursive calls2 db block gets30 consistent gets0 physical reads0 redo size2598 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Ne

38、t roundtrips to/from client0 sorts (memory)0 sorts (disk)14 rows processed通過以上分析，可以得出實(shí)際的執(zhí)行步驟是：1. TABLE ACCESS （FULL） OF EMP2. INDEX （UNIQUE SCAN） OF PK_DEPT （UNIQUE）3. TABLE ACCESS （BY INDEX ROWID） OF DEPT4. NESTED LOOPS （JOINING 1 AND 3）注： 目前許多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了極其方便的EXPLA

39、IN PLAN工具。也許喜歡圖形化界面的朋友們可以選用它們。24. 用索引提高效率索引是表的一個(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è)使用索引的好處是，它提供了主鍵（primary key）的唯一性驗(yàn)證。除了那些LONG或LONG RAW數(shù)據(jù)類型， 你可以索引幾乎所有的列。 通常， 在大型表中使用索引特別有效。 當(dāng)然，你也會(huì)發(fā)現(xiàn)， 在掃描小表時(shí)，使用索引同

40、樣能提高效率。雖然使用索引能得到查詢效率的提高，但是我們也必須注意到它的代價(jià)。 索引需要空間來存儲(chǔ)，也需要定期維護(hù)， 每當(dāng)有記錄在表中增減或索引列被修改時(shí)， 索引本身也會(huì)被修改。 這意味著每條記錄的INSERT ， DELETE ， UPDATE將為此多付出4 ， 5 次的磁盤I/O . 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理，那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢。譯者按：定期的重構(gòu)索引是有必要的。ALTER INDEX REBUILD 25. 索引的操作ORACLE對(duì)索引有兩種訪問模式。索引唯一掃描 （ INDEX UNIQUE SCAN）大多數(shù)情況下， 優(yōu)化器通過WHERE子句訪問INDEX

41、.例如：表LODGING有兩個(gè)索引 ： 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER.SELECT *FROM LODGINGWHERE LODGING = ROSE HILL；在內(nèi)部 ， 上述SQL將被分成兩步執(zhí)行， 首先 ， LODGING_PK 索引將通過索引唯一掃描的方式被訪問 ， 獲得相對(duì)應(yīng)的ROWID， 通過ROWID訪問表的方式執(zhí)行下一步檢索。如果被檢索返回的列包括在INDEX列中，ORACLE將不執(zhí)行第二步的處理（通過ROWID訪問表）。 因?yàn)闄z索數(shù)據(jù)保存在索引中， 單單訪問索引就可以完全滿足查詢

42、結(jié)果。下面SQL只需要INDEX UNIQUE SCAN 操作。SELECT LODGINGFROM LODGINGWHERE LODGING = ROSE HILL；索引范圍查詢（INDEX RANGE SCAN）適用于兩種情況：1. 基于一個(gè)范圍的檢索2. 基于非唯一性索引的檢索例1：SELECT LODGING FROM LODGING WHERE LODGING LIKE M%；WHERE子句條件包括一系列值， ORACLE將通過索引范圍查詢的方式查詢LODGING_PK . 由于索引范圍查詢將返回一組值， 它的效率就要比索引唯一掃描低一些。例2：SELECT LODGINGFROM LODGINGWHERE MANAGER = BILL GATES；這個(gè)SQL的執(zhí)行分兩步， LODGING$MANAGER的索引范圍查詢（得到所有符合條件記錄的