oracle數(shù)據(jù)庫性能優(yōu)化

1、oracle 數(shù)據(jù)庫性能優(yōu)化1. 選用適合的 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)常運行 analyze 命令，以增加數(shù)據(jù)庫中的

2、對象統(tǒng)計信息 (object statistics) 的準(zhǔn)確性。如果數(shù)據(jù)庫的優(yōu)化器模式設(shè)置為選擇性 (CHOOSE) ，那么實際的優(yōu)化器模式將和是否 運行過 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)化器。2. 訪問 Table 的方式 Oracle 采用兩種訪問表中記錄的方式：a. 全

3、表掃描全表掃描就是順序地訪問表中每條記錄。 Oracle 采用一次讀入多個數(shù)據(jù)塊 (database block) 的方式優(yōu)化全表掃描。b. 通過 ROWID 訪問表你可以采用基于 ROWID 的訪問方式情況，提高訪問表的效率， ROWID 包含了表中 記錄的物理位置信息Oracle采用索引(INDEX)實現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID) 之間的聯(lián)系。通常索引提供了快速訪問 ROWID 的方法，因此那些基于索引列的 查詢就可以得到性能上的提高。3. 共享 SQL 語句為了不重復(fù)解析相同的 SQL語句，在第一次解析之后，Oracle將SQL語句存放在內(nèi) 存中。這塊位于系統(tǒng)全局區(qū)域SGA

4、(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)存的使用?？上У氖荗racle只對簡單的表提供高速緩沖 (cache buffering)，這個功能并不適 用于多表連接查詢。數(shù)據(jù)庫管理員必須在in it.ora 中為這個區(qū)域設(shè)置合適的參數(shù)，當(dāng)這個內(nèi)存區(qū)域越大，就可以保留更多的語句，當(dāng)然被共享的可能性也就越大了。當(dāng)

5、你向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 synonymWork_city

6、public synonymPla nt_detailpublic synonymJill sal_limit private synonymWork_city public synonymPla nt_detailtable owner考慮一下下列SQL語句能否在這兩個用戶之間共享。SQL能否共享原因select max（sal_cap） from sal_limit;不能每個用戶都有一個private synonym -sal_limit ，它們是不同的對象select cou nt（*O from work_city where sdesc like 'NEW%'能兩個用

7、戶訪問相同的對象 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 是表的所有 者，對象不同。C. 兩個SQL語句中必須使用相同的名字的綁定變量（bind variables）例如：第一組的兩個 SQL語句是相同的（可以共享），而第二組中的兩個語句是不同的 （即使在運行時，賦于不同的綁定變量相同的值）a. selectp

8、in，namefrompeoplewherepin :=:blk1.pin;selecthpin，namefrompeoplewherepin :=:blk1.pin;b. selectpin，namefrompeoplewherepin =:blk1.ot_ind;selectpin，namefrompeoplewherepin =:blk1.ov_ind;4. 選擇最有效率的表名順序（只在基于規(guī)則的優(yōu)化器中有效）Oracle的解析器按照從右到左的順序處理FROM子句中的表名，因此 FROM子句中寫在最后的表（基礎(chǔ)表driving table）將被最先處理。在 FROM子句中包含多個表的情況

9、 下，你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。當(dāng)Oracle處理多個表時，會運用排序及合并的方式連接它們。首先，掃描第一個表（FROM子句中最后的那個表）并對記錄進(jìn)行派序，然后掃描第二個表（FROM 子句中最后第二個表），最后將所有從第二個表中檢索出的記錄 與第一個表中合適記錄進(jìn)行合并。例如：表TAB1 16 ，384 條記錄表TAB2 1 條記錄選擇TAB2作為基礎(chǔ)表(最好的方法)select count(*) from tabl, tab2 執(zhí)行時間 0.96 秒選擇TAB2作為基礎(chǔ)表(不佳的方法)select count(*) from tab2, tab1 執(zhí)行時間 26.09 秒如果有

10、3個以上的表連接查詢，那就需要選擇交叉表(intersectiontable)作為基礎(chǔ)表，交叉表是指那個被其他表所引用的表。例如：EMP表描述了 LOCATION 表和CATEGORY表的交集。SELECT *FROM LOCATION L ，CATEGORY：,EMP EWHEREE.EMP_NO BETWEEN1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E丄OCN =匚LOCN將比下列SQL更有效率SELECT *FROM EMP E ， LOCATION L ， CATEGORY：WHEREE.CAT_NO = C.CAT_NOAND E丄OCN =匚

11、LOCNAND E.EMP_NO BETWEEN1000 AND 20005. WHERE 子句中的連接順序Oracle采用自下而上的順序解析 WHERE子句，根據(jù)這個原理，表之間的連接必須寫 在其他 WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。例如：(低效，執(zhí)行時間156.3秒)SELECT FROM EMP EWHERESAL > 50000AND JOB = MANAGERAND 25 < (SELECT COUNT(*) FROM EMPWHEREMGR=E.EMPNO);(高效，執(zhí)行時間10.6秒)SELECT FROM EMP E

12、WHERE25 < (SELECT COUNT(*) FROM EMP WHEREMGR=E.EMPNO)AND SAL > 50000AND JOB = MANAGER6.SELECT 子句中避免使用 *'當(dāng)你想在SELECT子句中列出所有的 COLUMN時，使用動態(tài) SQL列引用*是一個 方便的方法。不幸的是，這是一個非常低效的方法。實際上，Oracle在解析的過程中，會將*依次轉(zhuǎn)換成所有的列名，這個工作是通過查詢數(shù)據(jù)字典完成的，這意味著將耗費更多 的時間。7. 減少訪問數(shù)據(jù)庫的次數(shù)當(dāng)執(zhí)行每條SQL語句時，Oracle在內(nèi)部執(zhí)行了許多工作：解析 SQL語句，估算索引 的

13、利用率，綁定變量，讀數(shù)據(jù)塊等等。由此可見，減少訪問數(shù)據(jù)庫的次數(shù)，就能實際上減少Oracle的工作量。例如，以下有三種方法可以檢索出雇員號等于0342或0291的職員。方法1(最低效)SELECT EMP_NAME SALARY GRADEFROM EMPWHEREEMP_NO= 342;SELECT EMP_NAME SALARY GRADE FROM EMPWHEREEMP_NO= 291;方法2(次低效)DECLARECURSORC1 (E_NO NUMBER) ISSELECT EMP_NAMESALARY GRADEFROM EMPWHEREEMP_NO= E_NO;BEGINOPEN

14、 C1(342);FETCH C1 INTO ，.,.;OPEN C1(291);FETCH C1 INTO ，.,.;CLOSE C1; END;方法3(高效)SELECT A.EMP_NAMEA.SALARY A.GRADE B.EMP_NAME B.SALAR Y B.GRADE FROM EMP A, EMP BWHEREA.EMP_NO = 342AND B.EMP_NO = 291;在SQL*Plus , SQL*Forms 和Pro*C 中重新設(shè)置 ARRAYSIZE 參數(shù)，可以增加每次數(shù)據(jù)庫訪問的檢索數(shù)據(jù)量，建議值為200。8. 使用DECODE 函數(shù)來減少處理時間使用DECO

15、DE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表。例如：SELECT COUNT(*) SUM(SAL)FROM EMPWHEREDEPT_NO = 0020AND ENAME LIKE 'SMITH%；SELECT COUNT(*) SUM(SAL)FROM EMPWHEREDEPT_NO = 0030AND ENAME LIKE 'SMITH%；你可以用DECODE函數(shù)高效地得到相同結(jié)果SELECT COUNT(DECODE(DEPT_N020，' X', NULL) D0020_COUNTCOUNT(DECODE(DEPT_NO80,' X

16、9;, NULL) D0030_COUNTSUM(DECODE(DEPT_NO20, SAL, NULL) D0020_SALSUM(DECODE(DEPT_NO30, SAL, NULL) D0030_SALFROM EMP WHEREENAME LIKE 'SMITH%；類似的，DECODE函數(shù)也可以運用于 GROUP BY 和ORDER BY 子句中。9. 整合簡單，無關(guān)聯(lián)的數(shù)據(jù)庫訪問如果你有幾個簡單的數(shù)據(jù)庫查詢語句，你可以把它們整合到一個查詢中（即使它們之間沒有關(guān)系）例如：SELECT NAMEFROM EMPWHEREEMP_NO= 1234;SELECT NAMEFROM

17、DPTWHEREDPT_NO =10;SELECT NAMEFROM CATWHERECAT_TYPE = RD ;上面的3個查詢可以被合并成一個：SELECT E.NAME， D.NAME， C.NAMEFROM CAT C ， DPT D ， EMP E，DUAL XWHERENVL( X', X.DUMMY) = NVL( 'X', E.ROWID(+) AND NVL('X',X.DUMMY) =NVL( 'X',D.ROWID(+)AND NVL('X',X.DUMMY) =NVL( 'X',C.

18、ROWID(+)AND E.EMP_NO(+) = 1234AND D.DEPT_NO(+) =10AND C.CAT_TYPE(+) = RD ;10. 刪除重復(fù)記錄最高效的刪除重復(fù)記錄方法（因為使用了 ROWID）DELETE FROM EMP EWHEREE.ROWID > (SELECT MIN(X.ROWID) FROM EMP XWHEREX.EMP_NO = E.EMP_NO);11. 用 TRUNCATE 替代 DELETE當(dāng)刪除表中的記錄時，在通常情況下，回滾段 (rollback segme nts) 用來存放可以被 恢復(fù)的信息。如果你沒有 COMMIT事務(wù)，Orac

19、le會將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地 說是恢復(fù)到執(zhí)行刪除命令之前的狀況 )而當(dāng)運用TRUNCATE時，回滾段不再存放任何可被恢復(fù)的信息。當(dāng)命令運行后，數(shù) 據(jù)不能被恢復(fù)。因此很少的資源被調(diào)用，執(zhí)行時間也會很短。(譯者按：TRUNCATE 只在刪除全表適用，TRUNCATE 是DDL不是DML)12. 盡量多使用COMMIT只要有可能，在程序中盡量多使用COMMIT，這樣程序的性能得到提高，需求也會因為COMMIT 所釋放的資源而減少： COMMIT 所釋放的資源：a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息。b. 被程序語句獲得的鎖c. redo log buffer中的空間d. Oracle 為管理上

20、述3種資源中的內(nèi)部花費(譯者按：在使用 COMMIT時必須要注意到事務(wù)的完整性，現(xiàn)實中效率和事務(wù)完整性 往往是魚和熊掌不可得兼)13. 計算記錄條數(shù)和一般的觀點相反，count(*) 比count(1)稍快，當(dāng)然如果可以通過索引檢索，對索 引列的計數(shù)仍舊是最快的。例如COUNT(EMPNO)(譯者按：在 CSDN論壇中，曾經(jīng)對此有過相當(dāng)熱烈的討論，作者的觀點并不十分準(zhǔn) 確，通過實際的測試，上述三種方法并沒有顯著的性能差別)14. 用Where 子句替換HAVING 子句避免使用HAVING子句，HAVING 只會在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾。 這個處理需要排序，總計等操作。如果能通過

21、WHERE子句限制記錄的數(shù)目那就能減少這方面的開銷。例如：低效：SELECT REGION AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION REGION != 'SYDNEYAND REGION != 'PERTH咼效：SELECT REGION AVG(LOG_SIZE)FROM LOCATIONWHEREREGION REGION !=' SYDNE'AND REGION !=' PERTHGROUP BY REGION15. 減少對表的查詢在含有子查詢的SQL語句中，要特別注意減少對表的

22、查詢。例如：低效SELECT TAB_NAMEFROM TABLESWHERETAB_NAME= ( SELECT TAB_NAMEFROM TAB_COLUMNSWHEREVERSION = 604)AND DB_VER= ( SELECT DB_VERFROM TAB_COLUMNSWHEREVERSION = 604)高效SELECT TAB_NAMEFROM TABLESWHERE(TAB_NAMJE DB_VER)=(SELECT TAB_NAMEDB_VER)FROM TAB_COLUMNSWHEREVERSION = 604)Update 多個 Column 例子：低效:UPDA

23、TE EMPSET EMP_CAT = (SELECT MAX(CATEGORYFROM EMP_CATEGORIES) SAL_RANGE= (SELECT MAX(SAL_RANGE)FROM EMP_CATEGORIES) WHEREEMP_DEPT= 0020;咼效：UPDATE EMPSET (EMP_CAT SAL_RANGE)=(SELECT MAX(CATEGORY) MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHEREEMP_DEPT= 0020;16. 通過內(nèi)部函數(shù)提高 SQL效率SELECT H.HIST_TYPE T.TYPE_DES,CCOU

24、NT(*)FROM HISTORY_TYPET, EMP E, EMP_HISTORYHWHEREH.EMPNO = E.EMPNOAND H.HIST_TYPE = T.HIST_TYPEGROUP BY H.EMPNOE.ENAME H.HIST_TYPE T.TYPE_DESC;通過調(diào)用下面的函數(shù)可以提高效率。FUNCTION LOOKUP_HIST_TYPE(TYPN NUMBER) RETURN VARCHAR2 ASTDESC VARCHAR2(30);CURSORC1 ISSELECT TYPE_DESCFROM HISTORY_TYPEWHEREHIST_TYPE = TYP;

25、BEGINOPEN C1;FETCH C1 INTO TDESC;CLOSE C1;RETURN (NVL(TDESC'?');END;FUNCTION LOOKUP_EMP(EMIN NUMBER) RETURN VARCHAR2ASENAME VARCHAR2(30);CURSORC1 ISSELECT ENAMEFROM EMPWHEREEMPNO=EMP;BEGINOPEN C1;FETCH C1 INTO ENAME;CLOSE C1;RETURN (NVL(ENAMJE '?')；END;SELECT H.EMPNO LOOKUP_EMP(H.EMP

26、,O)H.HIST_TYPE LOOKUP_HIST_TYPE(H.HIST_TYPECOUNT(*)FROM EMP_HISTORYHGROUP BY H.EMPNO , H.HIST_TYPE;(譯者按：經(jīng)常在論壇中看到如能不能用一個 SQL寫出。的貼子，殊不知復(fù)雜的SQL往往犧牲了執(zhí)行效率。能夠掌握上面的運用函數(shù)解決問題的方法在實際工作中是非常 有意義的)17. 使用表的別名(Alias)當(dāng)在SQL語句中連接多個表時，請使用表的別名并把別名前綴于每個Column上。這樣一來，就可以減少解析的時間并減少那些由Colum n歧義引起的語法錯誤。(譯者注：Column 歧義指的是由于 SQL中

27、不同的表具有相同的 Column 名，當(dāng)SQL 語句中出現(xiàn)這個Column時，SQL解析器無法判斷這個 Column的歸屬)18. 用 EXISTS 替代 IN在許多基于基礎(chǔ)表的查詢中，為了滿足一個條件，往往需要對另一個表進(jìn)行聯(lián)接。在 這種情況下，使用 EXISTS(或NOT EXISTS)通常將提高查詢的效率。低效：SELECT *FROM EMP (基礎(chǔ)表)WHEREEMPNO> 0AND DEPTNO IN (SELECT DEPTNOFROM DEPTWHERELOC = MELB)咼效：SELECT *FROM EMP （基礎(chǔ)表）WHEREEMPNO> 0AND EXIS

28、TS （SELECT'X'FROM DEPTWHEREDEPT.DEPTNO= EMP.DEPTNOAND LOC = MELB）（譯者按：相對來說，用 NOT EXISTS 替換NOT IN將更顯著地提高效率，下一節(jié)中 將指出）19. 用 NOT EXISTS 替代 NOT IN在子查詢中，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并。 無論在哪種情況下，NOT IN 都是最低效的（因為它對子查詢中的表執(zhí)行了一個全表遍歷 ）。為了避免使用 NOT IN，我 們可以把它改寫成外連接 （Outer Joi ns） 或NOT EXISTS 。例如：SELECT FROM EMPWHE

29、REDEPT_NO NOT IN (SELECT DEPT_NO FROM DEPTWHEREDEPT_CAT=A');為了提高效率。改寫為：(方法一：高效)SELECT .FROM EMP A, DEPT BWHEREA.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULLAND B.DEPT_CAT(+) =A(方法二：最高效)SELECT .FROM EMP EWHERENOT EXISTS (SELECT X'FROM DEPT DWHERED.DEPT_NO = E.DEPT_NOAND DEPT_CAT =A );20. 用表連接替換EX

30、ISTSSELECT ENAMEFROM EMP EWHEREEXISTS (SELECT X'FROM DEPTWHEREDEPT_NO = E.DEPT_NOAND DEPT_CAT = A );(更高效)SELECT ENAMEFROM DEPT D, EMP EWHEREE.DEPT_NO = D.DEPT_NO AND DEPT_CAT = A ;（譯者按：在RBO的情況下，前者的執(zhí)行路徑包括 FILTER，后者使用NESTED LOOP）21. 用 EXISTS 替換 DISTINCT當(dāng)提交一個包含一對多表信息（比如部門表和雇員表）的查詢時，避免在 SELECT子句中使用D

31、ISTINCT。一般可以考慮用 EXIST替換例如：低效：SELECT DISTINCT DEPT_NpDEPT_NAME FROM DEPT D, EMP EWHERED.DEPT_NO = E.DEPT_NO咼效：SELECT DEPT_N,ODEPT_NAMEFROM DEPT DWHEREEXISTS ( SELECT X'FROM EMP EWHEREE.DEPT_NO = D.DEPT_NO);EXISTS使查詢更為迅速，因為 RDBMS核心模塊將在子查詢的條件一旦滿足后，立 刻返回結(jié)果。22. 識別低效執(zhí)行'勺SQL語句用下列SQL工具找出低效SQL :SELEC

32、T EXECUTIONS,DISK_READ,S BUFFER_GETSROUND(BUFFER_GETS-DISK_READS)/BUFFER_GETSHit_radio ,ROUND(DISK_READS/EXECUTJONS Reads_per_run,SQL_TEXTFROM V$SQLAREAWHEREEXECUTIONS>0AND BUFFER_GETS> 0AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS0.8ORDER BY 4 DESC;(譯者按：雖然目前各種關(guān)于 SQL優(yōu)化的圖形化工具層出不窮，但是寫出自己的 SQL 工具來解決問

33、題始終是一個最好的方法)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_TRACETRUE設(shè)置SQL TRACE在整個數(shù)據(jù)庫有效仿，你必須將SQL_TRACE 參數(shù)在init.ora 中設(shè)為TRUE，USER_DUMP_DEST 參數(shù)說明了生成跟蹤文件的目錄(譯者按：這一節(jié)中，作者并沒有提到 TKPROF的用法，對SQL

34、 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是怎么樣連接表，使用什么方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱。你需要按

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

36、autotrace trace only /*trace only*/SQL> /14 rows selected.Executio nPla n0 SELECT STATEMENTptimizer=CHOOSE1 0NESTED LOOPS2 1TABLEACCESS (FULL)OF'EMP'3 1TABLEACCESS (BY INDEXROWID)OF 'DEPT'4 3INDEX(UNIQUE SCAN)OF'PK_DEPT' (UNIQUE)Statistics0recursivecalls2db blockgets30con

37、siste ntgets0physicalreads0redo size2598 bytessent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtripsto/fromclient0 sorts(memory)0 sorts(disk)14 rows processed通過以上分析，可以得出實際的執(zhí)行步驟是:1. TABLE ACCESS (FULL) OF 'EMP'2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQ

38、UE)3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'4. NESTED LOOPS (JOINING 1 AND 3)注：目前許多第三方的工具如TOAD 和Oracle 本身提供的工具如 OMS 的SQLAnalyze都提供了極其方便的 EXPLAIN PLAN 工具。也許喜歡圖形化界面的朋友們可以選 用它們。25. 用索引提高效率索引是表的一個概念部分，用來提高檢索數(shù)據(jù)的效率。實際上，Oracle使用了一個復(fù)雜的自平衡B-tree 結(jié)構(gòu)。通常，通過索引查詢數(shù)據(jù)比全表掃描要快。當(dāng)Oracle找出執(zhí)行查詢和Update語句的最佳路徑時， Or

39、acle優(yōu)化器將使用索引。同樣在聯(lián)結(jié)多個表時使用 索引也可以提高效率。另一個使用索引的好處是，它提供了主鍵(primary key)的唯一性驗證。除了那些LONG或LONG RAW 數(shù)據(jù)類型，你可以索引幾乎所有的列。通常，在大型 表中使用索引特別有效。當(dāng)然，你也會發(fā)現(xiàn)，在掃描小表時，使用索引同樣能提高效率。雖然使用索引能得到查詢效率的提高，但是我們也必須注意到它的代價。索引需要空 間來存儲，也需要定期維護(hù)，每當(dāng)有記錄在表中增減或索引列被修改時，索引本身也會被修改。這意味著每條記錄的 INSERT，DELETE，UPDATE 將為此多付出 4、5次的磁盤I/O。 因為索引需要額外的存儲空間和處理

40、，那些不必要的索引反而會使查詢反應(yīng)時間變慢。譯者按：定期的重構(gòu)索引是有必要的。ALTER INDEXREBUILD26. 索引的操作Oracle對索引有兩種訪問模式。索弓I唯一掃描(INDEX UNIQUE SCAN)大多數(shù)情況下，優(yōu)化器通過 WHERE子句訪問INDEX.例如：表LODGING 有兩個索引：建立在 LODGING 列上的唯一性索引 LODGING_PK 和 建立在MANAGER列上的非唯一性索引 LODGING$MANAGER.SELECT *FROM LODGINGWHERELODGING = ROSE HILL'；在內(nèi)部，上述SQL將被分成兩步執(zhí)行，首先，LODG

41、ING_PK 索引將通過索引唯一掃 描的方式被訪問，獲得相對應(yīng)的ROWID，通過ROWID訪問表的方式執(zhí)行下一步檢索。如果被檢索返回的列包括在INDEX 列中，Oracle 將不執(zhí)行第二步的處理 (通過ROWID訪問表)。因為檢索數(shù)據(jù)保存在索引中，單單訪問索引就可以完全滿足查詢結(jié)果。下面SQL只需要INDEX UNIQUE SCAN 操作。SELECT LODGINGFROM LODGINGWHERELODGING = ROSE HILL'索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況：1. 基于一個范圍的檢索2. 基于非唯一性索引的檢索例1 :SELECT LODGI

42、NG FROM LODGING WHERELODGING LIKE M%；WHERE 子句條件包括一系列值，Oracle 將通過索引范圍查詢的方式查詢 LODGING_PK .由于索引范圍查詢將返回一組值，它的效率就要比索引唯一掃描低一些。例2 :SELECT LODGINGFROM LODGINGWHEREMANAGER= BILL GATES ;這個SQL的執(zhí)行分兩步，LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的 ROWID)和下一步同過 ROWID 訪問表得到LODGING 列的值。由于 LODGING$MANAGER 是一個非唯一性的索引，數(shù)據(jù)庫不能對它執(zhí)行索引

43、唯一掃描。由于SQL返回LODGING 列，而它并不存在于 LODGING$MANAGER 索引中，所 以在索引范圍查詢后會執(zhí)行一個通過ROWID訪問表的操作。WHERE子句中，如果索引列所對應(yīng)的值的第一個字符由通配符(WILDCARD)開始，索引將不被采用。在這種情況下，Oracle將使用全表掃描。SELECT LODGING FROM LODGING WHEREMANAGERLIKE HANMAN27. 基礎(chǔ)表的選擇基礎(chǔ)表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問 )。根據(jù)優(yōu)化器的不同，SQL語句中基礎(chǔ)表的選擇是不一樣的。如果你使用的是 CBO (COST BASED OPTIMIZER)，優(yōu)化器會檢查 SQL語句中的 每個表的物理大小，索引的狀態(tài)，然后選用花費最低的執(zhí)行路徑。如果你用 RBO (RULE BASED OPTIMIZER)，并且所有的連接條件都有索引對應(yīng)，在這種情況下，基礎(chǔ)表就是 FROM子句中列在最后的那個表。blog舉例：SELECT A.NAME ， B.MANAGERFROM WORKER A ，LODGING BWHERE A. LODGING = B. LODING;由于LODGING表的LODING列上有一個索引，而且 WORKER表中沒有相比較的 索引，WORKER表將被作為查詢中的基礎(chǔ)表。28. 多個