oracle_sql性能優(yōu)化

1、 Oracle sql 性能優(yōu)化調整性能優(yōu)化調整1. 選用適合的選用適合的 ORACLE 優(yōu)化器優(yōu)化器 ORACLE 的優(yōu)化器共有 3 種: a. RULE (基于規(guī)則) b. COST (基于成本) c. CHOOSE (選擇性) 設置缺省的優(yōu)化器,可以通過對 init.ora 文件中 OPTIMIZER_MODE 參數的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你當然也在 SQL 句級或是會話(session)級對其進行覆蓋. 為了使用基于成本的優(yōu)化器(CBO, Cost-Based Optimizer) , 你必須經常運行 analyze

2、命令,以增加數據庫中的對象統(tǒng)計信息(object statistics)的準確性. 如果數據庫的優(yōu)化器模式設置為選擇性(CHOOSE),那么實際的優(yōu)化器模式將和是否運行過 analyze 命令有關. 如果 table 已經被 analyze 過, 優(yōu)化器模式將自動成為 CBO , 反之,數據庫將采用 RULE 形式的優(yōu)化器. 在缺省情況下,ORACLE 采用 CHOOSE 優(yōu)化器, 為了避免那些不必要的全表掃描(full table scan) , 你必須盡量避免使用 CHOOSE 優(yōu)化器,而直接采用基于規(guī)則或者基于成本的優(yōu)化器. 2. 訪問訪問 Table 的方式的方式 ORACLE 采用兩

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

4、 SQL 語句存放在內存中.這塊位于系統(tǒng)全局區(qū)域 SGA(system global area)的共享池(shared buffer pool)中的內存可以被所有的數據庫用戶共享. 因此,當你執(zhí)行一個 SQL 語句(有時被稱為一個游標)時,如果它和之前的執(zhí)行過的語句完全相同, ORACLE 就能很快獲得已經被解析的語句以及最好的執(zhí)行路徑. ORACLE 的這個功能大大地提高了 SQL 的執(zhí)行性能并節(jié)省了內存的使用. 可惜的是 ORACLE 只對簡單的表提供高速緩沖(cache buffering) ,這個功能并不適用于多表連接查詢.數據庫管理員必須在 init.ora 中為這個區(qū)域設置合適的參

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

6、相同:例如: 用戶對象名如何訪問Jacksal_limitprivate synonymWork_citypublic synonymPlant_detailpublic synonymJillsal_limitprivate synonymWork_citypublic synonymPlant_detailtable owner 考慮一下下列 SQL 語句能否在這兩個用戶之間共享. SQL能否共享能否共享原因原因select max(sal_cap) from sal_limit;不能每個用戶都有一個 private synonym - sal_limit , 它們是不同的對象select

7、 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 語句是相

8、同的(可以共享),而第二組中的兩個語句是不同的(即使在運行時,賦于不同的綁定變量相同的值)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;4. 選擇最有效率的表名順序選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中

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

10、 表 TAB2 1 條記錄 選擇 TAB2 作為基礎表 (最好的方法) select count(*) from tab1,tab2 執(zhí)行時間 0.96 秒 選擇 TAB2 作為基礎表 (不佳的方法) select count(*) from tab2,tab1 執(zhí)行時間 26.09 秒如果有 3 個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.例如: EMP 表描述了 LOCATION 表和 CATEGORY 表的交集.SELECT * FROM LOCATION L , CATEGORY C, EMP E WH

11、ERE 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 子句子句,根據這個原理,表之間的連接必須寫在其他 W

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

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

14、 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

15、;方法 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 中重新設置 ARRAYSIZE 參數, 可以增加每次數據庫訪問的檢索數據量 ,建議值為 200.8. 使用使用 DECODE 函數來減少處理時間函數來減少處理時間使用 DECODE 函數可以避免重復掃描相同記錄或重復連接相同的表.例如: SELE

16、CT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0020 AND ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKESMITH%;你可以用 DECODE 函數高效地得到相同結果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,N

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

18、 NAME FROM CATWHERE CAT_TYPE = RD;上面的 3 個查詢可以被合并成一個: SELECT E.NAME , D.NAME , C.NAMEFROM CAT C , DPT D , EMP E,DUAL XWHERE 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; (譯者

19、按譯者按: 雖然采取這種方法雖然采取這種方法,效率得到提高效率得到提高,但是程序的可讀性大大降低但是程序的可讀性大大降低,所以讀者所以讀者 還還是要權衡之間的利弊是要權衡之間的利弊)10. 刪除重復記錄刪除重復記錄最高效的刪除重復記錄方法 ( 因為使用了 ROWID)DELETE FROM EMP EWHERE E.ROWID (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);11. 用用 TRUNCATE 替代替代 DELETE當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被

20、恢復的信息. 如果你沒有 COMMIT 事務,ORACLE 會將數據恢復到刪除之前的狀態(tài)(準確地說是恢復到執(zhí)行刪除命令之前的狀況)而當運用 TRUNCATE 時, 回滾段不再存放任何可被恢復的信息.當命令運行后,數據不能被恢復.因此很少的資源被調用,執(zhí)行時間也會很短. (譯者按譯者按: TRUNCATE 只在刪除全表適用只在刪除全表適用,TRUNCATE 是是 DDL 不是不是 DML)12. 盡量多使用盡量多使用 COMMIT只要有可能,在程序中盡量多使用 COMMIT, 這樣程序的性能得到提高,需求也會因為COMMIT 所釋放的資源而減少: COMMIT 所釋放的資源:a. 回滾段上用于恢

21、復數據的信息.b. 被程序語句獲得的鎖c. redo log buffer 中的空間d. ORACLE 為管理上述 3 種資源中的內部花費 (譯者按譯者按: 在使用在使用 COMMIT 時必須要注意到事務的完整性時必須要注意到事務的完整性,現實中效率和事務完整性現實中效率和事務完整性往往是魚和熊掌不可得兼往往是魚和熊掌不可得兼)13. 計算記錄條數計算記錄條數 和一般的觀點相反, count(*) 比 count(1)稍快 , 當然如果可以通過索引檢索,對索引列的計數仍舊是最快的. 例如 COUNT(EMPNO) (譯者按譯者按: 在在 CSDN 論壇中論壇中,曾經對此有過相當熱烈的討論曾經對

22、此有過相當熱烈的討論, 作者的觀點并不十分準確作者的觀點并不十分準確,通過通過實際的測試實際的測試,上述三種方法并沒有顯著的性能差別上述三種方法并沒有顯著的性能差別) 14. 用用 Where 子句替換子句替換 HAVING 子句子句 避免使用 HAVING 子句, HAVING 只會在檢索出所有記錄之后才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過 WHERE 子句限制記錄的數目,那就能減少這方面的開銷. 例如: 低效: SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGIO

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

24、 例如: 低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) ANDDB_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) Updat

25、e 多個 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. 通過內

26、部函數提高通過內部函數提高 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.EMPNOAND H.HIST_TYPE = T.HIST_TYPEGROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;通過調用下面的函數可以提高效率.FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2AS TDES

27、C 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 VARCHAR2AS ENAME VARCHAR2(30); CURSOR C1 IS SELECT ENAME FROM EMP WHERE EMPNO=EMP;BEGIN OPEN C1; FET

28、CH 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; (譯者按譯者按: 經常在論壇中看到如經常在論壇中看到如 能不能用一個能不能用一個 SQL 寫出寫出. 的貼子的貼子, 殊不知復雜的殊不知復雜的 SQL往往犧牲了執(zhí)行效率往往犧牲了執(zhí)行效率. 能夠掌握上面的運用函數解決問題的

29、方法在實際工作中是非常有意能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的義的)17. 使用表的別名使用表的別名(Alias)當在 SQL 語句中連接多個表時, 請使用表的別名并把別名前綴于每個 Column 上.這樣一來,就可以減少解析的時間并減少那些由 Column 歧義引起的語法錯誤. (譯者注譯者注: Column 歧義指的是由于歧義指的是由于 SQL 中不同的表具有相同的中不同的表具有相同的 Column 名名,當當 SQL 語語句中出現這個句中出現這個 Column 時時,SQL 解析器無法判斷這個解析器無法判斷這個 Column 的歸屬的歸屬)18. 用用 EXIS

30、TS 替代替代 IN在許多基于基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯(lián)接.在這種情況下, 使用 EXISTS(或 NOT EXISTS)通常將提高查詢的效率. 低效:SELECT * FROM EMP (基礎表)WHERE EMPNO 0AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = MELB) 高效:SELECT * FROM EMP (基礎表)WHERE EMPNO 0AND EXISTS (SELECT X FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = MELB)

31、(譯者按譯者按: 相對來說相對來說,用用 NOT EXISTS 替換替換 NOT IN 將更顯著地提高效率將更顯著地提高效率,下一節(jié)中將指出下一節(jié)中將指出)19. 用用 NOT EXISTS 替代替代 NOT IN在子查詢中,NOT IN 子句將執(zhí)行一個內部的排序和合并. 無論在哪種情況下,NOT IN 都是最低效的 (因為它對子查詢中的表執(zhí)行了一個全表遍歷). 為了避免使用 NOT IN ,我們可以把它改寫成外連接(Outer Joins)或 NOT EXISTS. 例如:SELECT FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEP

32、T WHERE DEPT_CAT=A);為了提高效率.改寫為: (方法一: 高效)SELECT .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 X FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = A);20. 用表連接替換用表連接替換 EXISTS 通常來說 , 采用表連接的方式比 EXISTS 更有效

33、率 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í)行路徑包括前者的執(zhí)行路徑包括 FILTER,后者使用后者使用 NESTED LOOP) 21. 用用 EXISTS 替換替換 DISTINCT當提交一個包含一對多表信

34、息(比如部門表和雇員表)的查詢時,避免在 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 使查詢更為迅速,因為 RDBMS 核心模塊將在子查詢的條件一旦滿足后,立刻返回結

35、果. 22. 識別識別低效執(zhí)行低效執(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 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS list

36、1 SELECT * 2 FROM dept, emp 3* WHERE emp.deptno = dept.deptnoSQL set autotrace traceonly /*traceonly 可以不顯示執(zhí)行結果可以不顯示執(zhí)行結果*/SQL /14 rows selected.Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF EMP 3 1 TABLE ACCESS (BY INDEX ROWID) OF DEPT 4 3 INDEX (UNIQ

37、UE 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通過以上分析通過以上分

38、析,可以得出實際的執(zhí)行步驟是可以得出實際的執(zhí)行步驟是:1. TABLE ACCESS (FULL) OF EMP 2. 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 工具工具.也許喜歡圖形化界面的朋友們

39、可以選也許喜歡圖形化界面的朋友們可以選用它們用它們.25.25. 用索引提高效率用索引提高效率索引是表的一個概念部分,用來提高檢索數據的效率. 實際上,ORACLE 使用了一個復雜的自平衡 B-tree 結構. 通常,通過索引查詢數據比全表掃描要快. 當 ORACLE 找出執(zhí)行查詢和 Update 語句的最佳路徑時, ORACLE 優(yōu)化器將使用索引. 同樣在聯(lián)結多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.除了那些 LONG 或 LONG RAW 數據類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你

40、也會發(fā)現, 在掃描小表時,使用索引同樣能提高效率.雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的 INSERT , DELETE , UPDATE 將為此多付出 4 , 5 次的磁盤 I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.譯者按譯者按: : 定期的重構索引是有必要的定期的重構索引是有必要的. . ALTERALTER INDEXINDEX REBUILDREBUILD 26.26. 索引的操作索引的操作ORA

41、CLE 對索引有兩種訪問模式.索引唯一掃描 ( INDEX UNIQUE SCAN)大多數情況下, 優(yōu)化器通過 WHERE 子句訪問 INDEX.例如:表 LODGING 有兩個索引 : 建立在 LODGING 列上的唯一性索引LODGING_PK 和建立在 MANAGER 列上的非唯一性索引 LODGING$MANAGER. SELECT * FROM LODGINGWHERE LODGING = ROSE HILL; 在內部 , 上述 SQL 將被分成兩步執(zhí)行, 首先 , LODGING_PK 索引將通過索引唯一掃描的方式被訪問 , 獲得相對應的 ROWID, 通過 ROWID 訪問表的方

42、式 執(zhí)行下一步檢索. 如果被檢索返回的列包括在 INDEX 列中,ORACLE 將不執(zhí)行第二步的處理(通過 ROWID 訪問表). 因為檢索數據保存在索引中, 單單訪問索引就可以完全滿足查詢結果. 下面 SQL 只需要 INDEX UNIQUE SCAN 操作. SELECT LODGING FROM LODGINGWHERE LODGING = ROSE HILL; 索引范圍查詢(INDEX RANGE SCAN) 適用于兩種情況:1. 基于一個范圍的檢索2. 基于非唯一性索引的檢索 例 1: SELECT LODGING FROM LODGINGWHERE LODGING LIKE M%;

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

44、列,而它并不存在于 LODGING$MANAGER 索引中, 所以在索引范圍查詢后會執(zhí)行一個通過 ROWID 訪問表的操作. WHERE 子句中, 如果索引列所對應的值的第一個字符由通配符(WILDCARD)開始, 索引將不被采用. SELECT LODGING FROM LODGINGWHERE MANAGER LIKE HANMAN; 在這種情況下，ORACLE 將使用全表掃描.27.27. 基礎表的選擇基礎表的選擇基礎表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問). 根據優(yōu)化器的不同, SQL 語句中基礎表的選擇是不一樣的.如果你使用的是 CBO (CO

45、ST BASED OPTIMIZER),優(yōu)化器會檢查 SQL 語句中的每個表的物理大小,索引的狀態(tài),然后選用花費最低的執(zhí)行路徑.如果你用 RBO (RULE BASED OPTIMIZER) , 并且所有的連接條件都有索引對應, 在這種情況下, 基礎表就是 FROM 子句中列在最后的那個表.舉例: SELECT A.NAME , B.MANAGER FROMWORKER A, LODGING B WHEREA.LODGING = B.LODING;由于 LODGING 表的 LODING 列上有一個索引, 而且 WORKER 表中沒有相比較的索引, WORKER 表將被作為查詢中的基礎表.28

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

47、中最先被引用的索引將有最高的優(yōu)先級.舉例: DEPTNO 上有一個非唯一性索引,EMP_CAT 也有一個非唯一性索引. SELECT ENAME, FROM EMP WHERE DEPT_NO = 20 AND EMP_CAT = A;這里,DEPTNO 索引將被最先檢索,然后同 EMP_CAT 索引檢索出的記錄進行合并. 執(zhí)行路徑如下:TABLE ACCESS BY ROWID ON EMP AND-EQUAL INDEX RANGE SCAN ON DEPT_IDX INDEX RANGE SCAN ON CAT_IDX29.29. 等式比較和范圍比較等式比較和范圍比較 當 WHERE 子

48、句中有索引列, , ORACLE 不能合并它們,ORACLE 將用范圍比較. 舉例: DEPTNO 上有一個非唯一性索引,EMP_CAT 也有一個非唯一性索引. SELECT ENAME FROM EMP WHERE DEPTNO 20 AND EMP_CAT = A; 這里只有 EMP_CAT 索引被用到,然后所有的記錄將逐條與 DEPTNO 條件進行比較. 執(zhí)行路徑如下: TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON CAT_IDX30.30. 不明確的索引等級不明確的索引等級當 ORACLE 無法判斷索引的等級高低差別,優(yōu)化器將只使用一

49、個索引,它就是在 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譯者按譯者按: :我們來試一下以下這種情況我們來試一下以下這種情況: :SQLSQL selectselect index_name,index_name, unique

50、nessuniqueness fromfrom user_indexesuser_indexes wherewhere table_nametable_name = = EMP;EMP;INDEX_NAMEINDEX_NAME UNIQUENESUNIQUENES- -EMPNOEMPNO UNIQUEUNIQUEEMPTYPEEMPTYPE NONUNIQUENONUNIQUESQLSQL selectselect * * fromfrom empemp wherewhere empnoempno = 2 2 andand emp_typeemp_type = = AA ; ;nono ro

51、wsrows selectedselectedExecutionExecution PlanPlan- 0 0 SELECTSELECT STATEMENTSTATEMENT Optimizer=CHOOSEOptimizer=CHOOSE 1 1 0 0 TABLETABLE ACCESSACCESS (BY(BY INDEXINDEX ROWID)ROWID) OFOF EMPEMP 2 2 1 1 INDEXINDEX (RANGE(RANGE SCAN)SCAN) OFOF EMPTYPEEMPTYPE (NON-UNIQUE)(NON-UNIQUE) 雖然雖然 EMPNOEMPNO

52、是唯一性索引是唯一性索引, ,但是由于它所做的是范圍比較但是由于它所做的是范圍比較, , 等級要比非唯一等級要比非唯一性索引的等式比較低性索引的等式比較低! !31.31. 強制索引失效強制索引失效 如果兩個或以上索引具有相同的等級,你可以強制命令 ORACLE 優(yōu)化器使用其中的一個(通過它,檢索出的記錄數量少) .舉例: SELECT ENAMEFROM EMPWHERE EMPNO = 7935 AND DEPTNO + 0 = 10 /*DEPTNO/*DEPTNO 上的索引將失效上的索引將失效*/*/AND EMP_TYPE | = A /*EMP_TYPE/*EMP_TYPE 上的索

53、引將失效上的索引將失效*/*/這是一種相當直接的提高查詢效率的辦法. 但是你必須謹慎考慮這種策略,一般來說,只有在你希望單獨優(yōu)化幾個 SQL 時才能采用它.這里有一個例子關于何時采用這種策略, 假設在 EMP 表的 EMP_TYPE 列上有一個非唯一性的索引而 EMP_CLASS 上沒有索引. SELECT ENAMEFROM EMPWHERE EMP_TYPE = A AND EMP_CLASS = X;優(yōu)化器會注意到 EMP_TYPE 上的索引并使用它. 這是目前唯一的選擇. 如果,一段時間以后, 另一個非唯一性建立在 EMP_CLASS 上,優(yōu)化器必須對兩個索引進行選擇,在通常情況下,優(yōu)

54、化器將使用兩個索引并在他們的結果集合上執(zhí)行排序及合并. 然而,如果其中一個索引（EMP_TYPE）接近于唯一性而另一個索引（EMP_CLASS）上有幾千個重復的值. 排序及合并就會成為一種不必要的負擔. 在這種情況下,你希望使優(yōu)化器屏蔽掉 EMP_CLASS 索引.用下面的方案就可以解決問題.SELECT ENAMEFROM EMPWHERE EMP_TYPE = A AND EMP_CLASS| = X;32.32. 避免在索引列上使用計算避免在索引列上使用計算WHERE 子句中，如果索引列是函數的一部分優(yōu)化器將不使用索引而使用全表掃描舉例:低效：SELECT FROM DEPTWHERE

55、SAL * 12 25000;高效:SELECT FROM DEPTWHERE SAL 25000/12;譯者按譯者按: :這是一個非常實用的規(guī)則，請務必牢記這是一個非常實用的規(guī)則，請務必牢記33.33. 自動選擇索引自動選擇索引如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性在這種情況下，ORACLE 將使用唯一性索引而完全忽略非唯一性索引舉例:SELECT ENAMEFROM EMPWHERE EMPNO = 2326 AND DEPTNO = 20 ;這里，只有 EMPNO 上的索引是唯一性的，所以 EMPNO 索引將用來檢索記錄TABLE ACCESS BY

56、ROWID ON EMP INDEX UNIQUE SCAN ON EMP_NO_IDX 34.34. 避免在索引列上使用避免在索引列上使用 NOTNOT通常，我們要避免在索引列上使用 NOT, NOT 會產生在和在索引列上使用函數相同的影響. 當 ORACLE”遇到”NOT,他就會停止使用索引轉而執(zhí)行全表掃描. 舉例: 低效: (這里,不使用索引) SELECT FROM DEPT WHERE DEPT_CODE NOT = 0; 高效: (這里,使用了索引) SELECT FROM DEPT WHERE DEPT_CODE 0; 需要注意的是需要注意的是, ,在某些時候在某些時候, , O

57、RACLEORACLE 優(yōu)化器會自動將優(yōu)化器會自動將 NOTNOT 轉化成相對應的關轉化成相對應的關系操作符系操作符. NOT to = to NOT = NOT 譯者按譯者按: : 在這個例子中在這個例子中, ,作者犯了一些錯誤作者犯了一些錯誤. . 例子中的低效率例子中的低效率 SQLSQL 是不能被執(zhí)行的是不能被執(zhí)行的. .我做了一些測試我做了一些測試: : SQLSQL selectselect * * fromfrom empemp wherewhere NOTNOT empnoempno 1;1;nono rowsrows selectedselectedExecutionExec

58、ution PlanPlan- 0 0 SELECTSELECT STATEMENTSTATEMENT Optimizer=CHOOSEOptimizer=CHOOSE 1 1 0 0 TABLETABLE ACCESSACCESS (BY(BY INDEXINDEX ROWID)ROWID) OFOF EMPEMP 2 2 1 1 INDEXINDEX (RANGE(RANGE SCAN)SCAN) OFOF EMPNOEMPNO (UNIQUE)(UNIQUE) SQLSQL selectselect * * fromfrom empemp wherewhere empnoempno =替

59、代替代 如果 DEPTNO 上有一個索引, 高效: SELECT * FROM EMP WHERE DEPTNO =4 低效: SELECT * FROM EMP WHERE DEPTNO 3 兩者的區(qū)別在于, 前者 DBMS 將直接跳到第一個 DEPT 等于 4 的記錄而后者將首先定位到 DEPTNO=3 的記錄并且向前掃描到第一個 DEPT 大于 3 的記錄.36.36. 用用 UNIONUNION 替換替換 OROR ( (適用于索引列適用于索引列) )通常情況下, 用 UNION 替換 WHERE 子句中的 OR 將會起到較好的效果. 對索引列使用 OR 將造成全表掃描. 注意, 以上

60、規(guī)則只針對多個索引列有效. 如果有column 沒有被索引, 查詢效率可能會因為你沒有選擇 OR 而降低. 在下面的例子中, LOC_ID 和 REGION 上都建有索引.高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID