ORACLESQL性能優(yōu)化系列

1、實用文案ORACLE SQL 性能優(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) 級對其進行覆 蓋.為了使用基于成本的優(yōu)化器 (CBO, Cost-Based Optimizer) ,你必須經(jīng)常運行 analyze

2、 命令 ,以增加數(shù)據(jù)庫中的對象統(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

3、采用兩種訪問表中記錄的方式 :a. 全表掃描全表掃描就是順序地訪問表中每條記錄 . 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語句，在第一次解析之后

4、，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ù)庫管理員必須在 ini

5、t.ora 中為這個區(qū)域設(shè)置合適的參數(shù) ,當(dāng)這個內(nèi)存區(qū)域越大 ,就可以保留更多的語句 當(dāng)然被共享的可能性也就越大了 .當(dāng)你向 ORACLE 提交一個 SQL 語句 ,ORACLE 會首先在這塊內(nèi)存中查找相同的語句 .這里需要注明的是 ,ORACLE 對兩者采取的是一種嚴(yán)格匹配 ,要達成共享 ,SQL 語句必須完全相同 (包括空格 ,換行等 ).共享的語句必須滿足三個條件 :A. 字符級的比較 :當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同 .例如:SELECT * FROM EMP;和下列每一個都不同SELECT * from EMP;Select * From Emp;SELECT * FR

6、OM EMP;B. 兩個語句所指的對象必須完全相同 :例如:用戶 對象名 如何訪問Jack sal_limit private synonymWork_city public synonymPlant_detail public synonymJill sal_limit private synonymWork_city public synonymPlant_detail table owner考慮一下下列 SQL 語句能否在這兩個用戶之間共享 .SQL能否共享原因select max（sal_cap） from sal_limit;不能 每個用戶都有一個 private synonym -

7、 sal_limit ,它們是不同的對象select count（*0 from work_city where sdesc like NEW%;能兩個用戶訪問相同的對象 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

8、variables）例如：第一組的兩個 SQL 語句是相同的 （可以共享 ）,而第二組中的兩個語句是不同的 （即使在運行時 ,賦于不同的綁 定變量相同的值 ）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;ORA

9、CLE SQL 性能優(yōu)化系列（ 二）4. 選擇最有效率的表名順序 （ 只在基于規(guī)則的優(yōu)化器中有效 ）ORACLE 的解析器按照從右到左的順序處理 FROM 子句中的表名 ,因此 FROM 子句中寫在最后的表 （基礎(chǔ) 表 driving table） 將被最先處理 . 在 FROM 子句中包含多個表的情況下 ,你必須選擇記錄條數(shù)最少的表作為 基礎(chǔ)表 .當(dāng) ORACLE 處理多個表時 , 會運用排序及合并的方式連接它們 .首先 ,掃描第一個表 （FROM 子句中最 后的那個表 ） 并對記錄進行派序 ,然后掃描第二個表 （FROM 子句中最后第二個表 ）,最后將所有從第二個表中 檢索出的記錄與第一個

10、表中合適記錄進行合并 .例如:表 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

11、 LOCATION L ,CATEGORY 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ù)這個原理 ,表之間的連接

12、必須寫在其他 WHERE 條件 之前 , 那些可以過濾掉最大數(shù)量記錄的條件必須寫在 WHERE 子句的末尾 .例如:(低效 ,執(zhí)行時間 156.3 秒 )SELECT FROM EMP EWHERE SAL 50000AND JOB =MANAGER AND 25 (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO);(高效 ,執(zhí)行時間 10.6 秒)SELECT FROM EMP EWHERE 25 50000AND JOB =MANAGER ;6. SELECT 子句中避免使用 * 當(dāng)你想在 SELECT 子句中列出所有的 COLUMN 時,使用動態(tài) SQ

13、L 列引用 * 是一個方便的方法 .不幸的 是，這是一個非常低效的方法.實際上ORACLE在解析的過程中，會將*依次轉(zhuǎn)換成所有的列名，這個 工作是通過查詢數(shù)據(jù)字典完成的 , 這意味著將耗費更多的時間 .7. 減少訪問數(shù)據(jù)庫的次數(shù)當(dāng)執(zhí)行每條 SQL 語句時 ， ORACLE 在內(nèi)部執(zhí)行了許多工作 : 解析 SQL 語句 ， 估算索引的利用率 ， 綁定變量 讀數(shù)據(jù)塊等等 . 由此可見 ， 減少訪問數(shù)據(jù)庫的次數(shù) ， 就能實際上減少 ORACLE 的工作量 .例如，以下有三種方法可以檢索出雇員號等于 0342 或 0291 的職員 .方法 1 ( 最低效 )SELECT EMP_NAME ， SALA

14、RY ， GRADEFROM EMPWHERE EMP_NO = 342;SELECT EMP_NAME ， SALARY ， GRADEFROM 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

15、 ， A.SALARY ， A.GRADE，B.EMP_NAME ， B.SALARY ， B.GRADEFROM EMP A，EMP BWHERE A.EMP_NO = 342AND B.EMP_NO = 291;注意:在 SQL*Plus ， SQL*Forms 和 Pro*C 中重新設(shè)置 ARRAYSIZE 參數(shù) ， 可以增加每次數(shù)據(jù)庫訪問的檢索數(shù)據(jù) 量 ，建議值為 200ORACLE SQL 性能優(yōu)化系列 (三 )8. 使用 DECODE 函數(shù)來減少處理時間使用 DECODE 函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表 .例如:SELECT COUNT(*) ，SUM(SAL)FR

16、OM EMPWHERE DEPT_NO = 0020AND ENAME LIKESMITH% ;SELECT COUNT(*) ，SUM(SAL)FROM EMP標(biāo)準(zhǔn)文檔實用文案WHERE DEPT_NO = 0030AND ENAME LIKESMITH% ;你可以用 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,

17、 SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SALFROM EMP WHERE ENAME LIKE SMITH% ;類似的 ,DECODE 函數(shù)也可以運用于 GROUP BY 和 ORDER BY 子句中 .9. 整合簡單 , 無關(guān)聯(lián)的數(shù)據(jù)庫訪問如果你有幾個簡單的數(shù)據(jù)庫查詢語句 ,你可以把它們整合到一個查詢中 (即使它們之間沒有關(guān)系 ) 例如:SELECT NAMEFROM EMPWHERE EMP_NO = 1234;SELECT NAMEFROM DPTWHERE DPT_NO = 10 ;SELECT NAMEFROM CATWHERE CAT_T

18、YPE = RD ;上面的 3 個查詢可以被合并成一個 :SELECT E.NAME , D.NAME , C.NAMEWHERE 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( ) = 1234AND D.DEPT_NO( ) = 10AND C.CAT_TYPE( ) = RD;(譯者按 : 雖然采取這種方法 ,效率得到提高 ,但是程序的可讀性大大降低 ,所以讀者 還是要權(quán)衡之間的

19、利弊 )10. 刪除重復(fù)記錄最高效的刪除重復(fù)記錄方法 ( 因為使用了 ROWID)DELETE FROM EMP EWHERE E.ROWID (SELECT MIN(X.ROWID)FROM EMP XWHERE 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)運用 TRUNCATE 時 , 回滾段不再存放任

20、何可被恢復(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 為管理上述 3 種資源中的內(nèi)部花費(譯者按 : 在使用 COMMIT 時必須要注意到事務(wù)

21、的完整性 ,現(xiàn)實中效率和事務(wù)完整性往往是魚和熊掌不可得對該文的評論 人氣： 4369black_snail (2003-9-7 19:16:56)如果 DECODE 取值為 NULL ， SUM (NULL )的值是 NULL - 如果所有的值都是 NULL , SUM(NULL) =NULL 但是只要有一個值不是 NULL,SUM() NULL所以原 SQL 應(yīng)該沒有什么邏輯上的問題menlion (2003-9-4 12:38:01)關(guān)于第八點的個人看法：如果 DECODE 取值為 NULL ，SUM (NULL )的值是 NULL ，不會正常求和的。 可以改成如下所示就好了： SELEC

22、T COUNT(DECODE(DEPT_NO,0020, X ,NULL) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030, X,NULL) D0030_COUNT,SUM(DECODE(DEPT_NO,0020,SAL,0) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,0)D0030_SAL FROM EMP WHERE ENAME LIKESMITH% ;ORACLE SQL 性能優(yōu)化系列( 四)13. 計算記錄條數(shù)和一般的觀點相反，count(*)比count稍快，當(dāng)然如果可以通過索引檢索，對索引列的計數(shù)仍舊是最快的 . 例

23、如 COUNT(EMPNO)(譯者按 : 在 CSDN 論壇中，曾經(jīng)對此有過相當(dāng)熱烈的討論 ， 作者的觀點并不十分準(zhǔn)確 ，通過實際的測試 ，上述 三種方法并沒有顯著的性能差別 )14. 用 Where 子句替換 HAVING 子句避免使用 HAVING 子句 ， HAVING 只會在檢索出所有記錄之后才對結(jié)果集進行過濾 . 這個處理需要排序 ， 總計等操作 . 如果能通過 WHERE 子句限制記錄的數(shù)目 ，那就能減少這方面的開銷 .例如 :低效:SELECT REGION ，AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION REGIO

24、N != SYDNEY AND REGION != PERTH 高效SELECT REGION ， AVG(LOG_SIZE)FROM LOCATIONWHERE REGION REGION != SYDNEY AND REGION != PERTH GROUP BY REGION(譯者按 : HAVING 中的條件一般用于對一些集合函數(shù)的比較 ,如 COUNT() 等等 . 除此而外 ,一般的條件應(yīng) 該寫在 WHERE 子句中 )15. 減少對表的查詢在含有子查詢的 SQL 語句中 ,要特別注意減少對表的查詢 .例如:低效SELECT TAB_NAMEFROM TABLESWHERE TAB

25、_NAME = ( SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)AND DB_VER= ( SELECT DB_VERFROM TAB_COLUMNSWHERE VERSION = 604)高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHERE VERSION = 604)Update 多個 Column 例子 :低效:UPDATE EMPSET EMP_CAT = (SELECT MAX(CA

26、TEGORY) FROM EMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;高效: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(*

27、)FROM HISTORY_TYPE T,EMP E,EMP_HISTORY HWHERE H.EMPNO = E.EMPNOAND H.HIST_TYPE = T.HIST_TYPEGROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC; 通過調(diào)用下面的函數(shù)可以提高效率 .FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2 ASTDESC VARCHAR2(30);CURSOR C1 ISSELECT TYPE_DESCFROM HISTORY_TYPEWHERE HIST_TYPE =

28、TYP;BEGINOPEN C1;FETCH C1 INTO TDESC;CLOSE C1;RETURN (NVL(TDESC, ? );END;FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2ASENAME VARCHAR2(30);CURSOR C1 ISSELECT ENAMEFROM EMPWHERE EMPNO=EMP;BEGINOPEN C1;標(biāo)準(zhǔn)文檔實用文案FETCH C1 INTO ENAME;CLOSE C1;RETURN (NVL(ENAME, ?);END;SELECT H.EMPNO,LOOKUP_EMP(H.EMPN

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

30、mn 歧義引起的語法錯誤 .(譯者注：Column歧義指的是由于 SQL中不同的表具有相同的 Column名，當(dāng) SQL語句中出現(xiàn)這個 Column 時,SQL解析器無法判斷這個 Column的歸屬)18. 用 EXISTS 替代 IN在許多基于基礎(chǔ)表的查詢中 ，為了滿足一個條件 ，往往需要對另一個表進行聯(lián)接 .在這種情況下 ， 使用 EXISTS(或NOT EXISTS)通常將提高查詢的效率.低效 ：SELECT *FROM EMP ( 基礎(chǔ)表)WHERE EMPNO 0FROM DEPTWHERE LOC = MELB ）高效:SELECT *FROM EMP （ 基礎(chǔ)表 ）WHERE E

31、MPNO 0AND EXISTS （SELECT XFROM DEPTWHERE DEPT.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 Joins） 或 NOT EXISTS.

32、例如:SELECT FROM EMPWHERE DEPT_NO NOT IN （SELECT DEPT_NOFROM DEPTWHERE DEPT_CAT= A ）;為了提高效率 .改寫為 :（方法一 : 高效 ）SELECT .標(biāo)準(zhǔn)文檔實用文案FROM EMP A,DEPT BWHERE A.DEPT_NO = B.DEPT( )AND B.DEPT_NO IS NULLAND B.DEPT_CAT( ) = A (方法二 : 最高效 )SELECT .FROM EMP EWHERE NOT EXISTS (SELECT XFROM DEPT DWHERE D.DEPT_NO = E.DEP

33、T_NOAND DEPT_CAT = A );ORACLE SQL 性能優(yōu)化系列( 六)20. 用表連接替換 EXISTS通常來說 , 采用表連接的方式比 EXISTS 更有效率SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT XFROM DEPTWHERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = A );(更高效 )SELECT ENAME標(biāo)準(zhǔn)文檔實用文案FROM DEPT D,EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT = A ;(譯者按：在RBO的情況下，前者的執(zhí)行路徑包括 FI

34、LTER,后者使用NESTED LOOP)21. 用 EXISTS 替換 DISTINCT當(dāng)提交一個包含一對多表信息(比如部門表和雇員表)的查詢時，避免在SELECT子句中使用DISTINCT. 可以考慮用 EXIST 替換例如 ：低效：SELECT DISTINCT DEPT_NO，DEPT_NAMEFROM DEPT D，EMP EWHERE D.DEPT_NO = E.DEPT_NO高效：SELECT DEPT_NO，DEPT_NAMEFROM DEPT DWHERE EXISTS ( SELECT XFROM EMP EWHERE E.DEPT_NO = D.DEPT_NO);EXIS

35、TS 使查詢更為迅速 ，因為 RDBMS 核心模塊將在子查詢的條件一旦滿足后，立刻返回結(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_TEXTFROM V$SQLAREAWHERE EXECUTIONS0AND BUFFER_GETS 0AND （BUFFER_GETS

36、-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 Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 NESTED LOOPS2 1 TABLE ACCESS (FULL) OF EMP3 1 TABLE ACCESS (BY INDEX ROWID) OF DEPT4 3

37、 INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)Statistics0 recursive calls 2 db block gets 30 consistent gets0 physical reads0 redo size2598 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)14 rows processed通過以上分析 ,可以

38、得出實際的執(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 都提供了極其 方便的 EXPLAIN PLAN 工具 .也許喜歡圖形化界面的朋友們可以選用它們.ORACLE SQL 性能優(yōu)化系列 ( 八)25. 用索引提高效率索引是表的一個

39、概念部分 ,用來提高檢索數(shù)據(jù)的效率 . 實際上 ,ORACLE 使用了一個復(fù)雜的自平衡 B-tree 結(jié) 構(gòu).通常,通過索引查詢數(shù)據(jù)比全表掃描要快當(dāng)ORACLE找出執(zhí)行查詢和 Update語句的最佳路徑時，ORACLE 優(yōu)化器將使用索引 . 同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率 . 另一個使用索引的好處是 ,它 提供了主鍵 (primary key) 的唯一性驗證 .除了那些 LONG 或 LONG RAW 數(shù)據(jù)類型 , 你可以索引幾乎所有的列 . 通常, 在大型表中使用索引特別有 效. 當(dāng)然,你也會發(fā)現(xiàn) , 在掃描小表時 ,使用索引同樣能提高效率 .雖然使用索引能得到查詢效率的提高,但是

40、我們也必須注意到它的代價 . 索引需要空間來存儲 ,也需要定期維護 , 每當(dāng)有記錄在表中增減或索引列被修改時, 索引本身也會被修改 . 這意味著每條記錄的 INSERT , DELETE , UPDATE 將為此多付出 4 , 5 次的磁盤 I/O . 因為索引需要額外的存儲空間和處 理,那些不必要的索引反而會使查詢反應(yīng)時間變慢.譯者按 :定期的重構(gòu)索引是有必要的 .ALTER INDEX REBUILD 26. 索引的操作ORACLE 對索引有兩種訪問模式 .索引唯一掃描 ( INDEX UNIQUE SCAN)大多數(shù)情況下 , 優(yōu)化器通過 WHERE 子句訪問 INDEX.例如:表 LOD

41、GING 有兩個索引 : 建立在 LODGING 列上的唯一性索引 LODGING_PK 和建立在 MANAGER 列 上的非唯一性索引 LODGING$MANAGER.SELECT *FROM LODGINGWHERE LODGING = ROSE HILL ;在內(nèi)部 , 上述 SQL 將被分成兩步執(zhí)行 , 首先 , LODGING_PK 索引將通過索引唯一掃描的方式被訪問 , 獲得相對應(yīng)的 ROWID, 通過 ROWID 訪問表的方式 執(zhí)行下一步檢索 .如果被檢索返回的列包括在 INDEX列中ORACLE將不執(zhí)行第二步的處理(通過ROWID訪問表).因為檢索 數(shù)據(jù)保存在索引中 , 單單訪問

42、索引就可以完全滿足查詢結(jié)果 .下面 SQL 只需要 INDEX UNIQUE SCAN 操作.SELECT LODGINGFROM LODGINGWHERE LODGING =ROSE HILL;索引范圍查詢 (INDEX RANGE SCAN)適用于兩種情況 :1. 基于一個范圍的檢索2. 基于非唯一性索引的檢索例 1:SELECT LODGINGFROM LODGINGWHERE LODGING LIKE M% ;WHERE 子句條件包括一系列值 , ORACLE 將通過索引范圍查詢的方式查詢 LODGING_PK . 由于索引范圍 查詢將返回一組值 , 它的效率就要比索引唯一掃描低一些

43、.例 2:SELECT LODGINGFROM LODGINGWHERE MANAGER = BILL GATES ;這個SQL的執(zhí)行分兩步丄ODGING$MANAGER 的索引范圍查詢(得到所有符合條件記錄的 ROWID)和下 一步同過 ROWID 訪問表得到 LODGING 列的值. 由于 LODGING$MANAGER 是一個非唯一性的索引 ,數(shù) 據(jù)庫不能對它執(zhí)行索引唯一掃描 .由于SQL返回LODGING列，而它并不存在于LODGING$MANAGER 索引中，所以在索引范圍查詢后會執(zhí) 行一個通過 ROWID 訪問表的操作 .WHERE 子句中 ， 如果索引列所對應(yīng)的值的第一個字符由通

44、配符 (WILDCARD) 開始， 索引將不被采用 .SELECT LODGINGFROM LODGINGWHERE MANAGER LIKE HANMAN ;在這種情況下， ORACLE 將使用全表掃描 .ORACLE SQL 性能優(yōu)化系列 ( 九)27. 基礎(chǔ)表的選擇基礎(chǔ)表 (Driving Table) 是指被最先訪問的表 (通常以全表掃描的方式被訪問 ). 根據(jù)優(yōu)化器的不同 , SQL 語句 中基礎(chǔ)表的選擇是不一樣的 .如果你使用的是 CBO (COST BASED OPTIMIZER), 優(yōu)化器會檢查 SQL 語句中的每個表的物理大小 , 索引的 狀態(tài) ,然后選用花費最低的執(zhí)行路徑

45、.如果你用 RBO (RULE BASED OPTIMIZER) , 并且所有的連接條件都有索引對應(yīng) , 在這種情況下 , 基礎(chǔ)表 就是 FROM 子句中列在最后的那個表 .舉例 :SELECT A.NAME , B.MANAGERFROM WORKER A,LODGING BWHERE A.LODGING = B.LODING;由于 LODGING 表的 LODING 列上有一個索引 , 而且 WORKER 表中沒有相比較的索引 , WORKER 表將被 作為查詢中的基礎(chǔ)表 .28. 多個平等的索引當(dāng) SQL 語句的執(zhí)行路徑可以使用分布在多個表上的多個索引時 , ORACLE 會同時使用多個

46、索引并在運行時 對它們的記錄進行合并 , 檢索出僅對全部索引有效的記錄 .在 ORACLE 選擇執(zhí)行路徑時 , 唯一性索引的等級高于非唯一性索引 . 然而這個規(guī)則只有.如果索引列和其他表的索引類相比較這種子句在優(yōu)化器中的, FROM 子句中表的順序?qū)Q定哪個會被率先使用 . FROM 子當(dāng) WHERE 子句中索引列和常量比較才有效 等級是非常低的 .如果不同表中兩個想同等級的索引將被引用句中最后的表的索引將有最高的優(yōu)先級如果相同表中兩個想同等級的索引將被引用, WHERE 子句中最先被引用的索引將有最高的優(yōu)先級 舉例 :DEPTNO 上有一個非唯一性索引 ,EMP_CAT 也有一個非唯一性索引

47、SELECT ENAME,FROM EMPWHERE DEPT_NO = 20AND EMP_CAT = A ;這里 ,DEPTNO 索引將被最先檢索 ,然后同 EMP_CAT 索引檢索出的記錄進行合并 . 執(zhí)行路徑如下 :TABLE ACCESS BY ROWID ON EMPAND-EQUALINDEX RANGE SCAN ON DEPT_IDXINDEX RANGE SCAN ON CAT_IDX29. 等式比較和范圍比較當(dāng) WHERE 子句中有索引列 , ORACLE 不能合并它們 ,ORACLE 將用范圍比較 .舉例 :DEPTNO 上有一個非唯一性索引 ,EMP_CAT 也有一個

48、非唯一性索引 .SELECT ENAMEFROM EMPWHERE DEPTNO 20AND EMP_CAT = A ;這里只有 EMP_CAT 索引被用到 ,然后所有的記錄將逐條與 DEPTNO 條件進行比較 . 執(zhí)行路徑如下 :TABLE ACCESS BY ROWID ON EMPINDEX RANGE SCAN ON CAT_IDX30. 不明確的索引等級當(dāng) ORACLE 無法判斷索引的等級高低差別 , 優(yōu)化器將只使用一個索引 ,它就是在 WHERE 子句中被列在最前 面的 .舉例 :DEPTNO 上有一個非唯一性索引 ,EMP_CAT 也有一個非唯一性索引 .SELECT ENAME

49、FROM EMPWHERE DEPTNO 20AND EMP_CAT A ;這里, ORACLE 只用到了 DEPT_NO 索引. 執(zhí)行路徑如下 :TABLE ACCESS BY ROWID ON EMPINDEX RANGE SCAN ON DEPT_IDX譯者按 :我們來試一下以下這種情況 :SQL select index_name, uniqueness from user_indexes where table_name = EMP;INDEX_NAME UNIQUENESEMPNO UNIQUEEMPTYPE NONUNIQUESQL select * from emp where

50、 empno = 2 and emp_type = A ; no rows selectedExecution Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF EMP2 1 INDEX (RANGE SCAN) OF EMPTYPE (NON-UNIQUE)雖然 EMPNO 是唯一性索引 ,但是由于它所做的是范圍比較 , 等級要比非唯一性索引的等式比較低ORACLE SQL性能優(yōu)化系列 （ 十）31. 強制索引失效如果兩個或以上索引具有相同的等級 錄數(shù)量少 ） .,你可以強制命令 ORACL

51、E 優(yōu)化器使用其中的一個 （通過它 ,檢索出的記舉例 :SELECT ENAMEFROM EMPWHERE EMPNO = 7935上的索引將失效 */AND EMP_TYPE |A/*EMP_TYPE 上的索引將失效 */AND DEPTNO 0 = 10 /*DEPTNO這是一種相當(dāng)直接的提高查詢效率的辦法 . 但是你必須謹慎考慮這種策略 ,一般來說 ,只有在你希望單獨優(yōu)化幾個 SQL 時才能采用它 .這里有一個例子關(guān)于何時采用這種策略 ,假設(shè)在 EMP 表的 EMP_TYPE 列上有一個非唯一性的索引而 EMP_CLASS 上沒有索引 .SELECT ENAMEFROM EMPWHERE EMP_TYPE = A AND EMP_CLASS = X ;優(yōu)化器會注意到 EMP_TYPE 上的索引并使用它 . 這是目前唯一的選擇 . 如