OracleSQL優(yōu)化

1、實用標準文檔( 摘錄自互聯(lián)網(wǎng) )一： Oracle 與 SQL 相關的幾個概念1. 選用適合的 ORACLE 優(yōu)化器ORACLE 的優(yōu)化器共有 3 種 :a. RULE (基于規(guī)則)b. COST ( 基于成本 ) c. CHOOSE ( 選擇性)設 置缺省的優(yōu)化器 , 可以通過對 init.ora 文件中 OPTIMIZER_MODE 參數(shù)的各 種聲明，如 RULE,COST,CHOOSE, ALL_ROWS,FIRST_ROWS .你當然也在 SQL 句級或是會話 (session) 級對其進行覆蓋 . 為了使用基于成本的優(yōu)化器 (CBO, Cost-Based Optimizer) ,

2、你必須經(jīng)常運行 analyze 命令 , 以增加數(shù)據(jù)庫中的對 象統(tǒng)計信息(object statistics)的準確性.如果數(shù)據(jù)庫的優(yōu)化器模式設置為選擇 性(CHOOSE),那么實際的優(yōu)化器模式將和是否運行過 analyze命令有關.如果 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 的

3、方式ORACLE 采用兩種訪問表中記錄的方式 :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 語句為 了不重復解析相同的 SQL 語句,

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

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

6、ECT * FROM EMP;B. 兩個語句所指的對象必須完全相同例如：用戶 對象名 如何訪問Jacksal_limitWork_cityPla nt_detailJillsal_limitWork_cityPlant detailprivate synonym public synonym public synonym private synonym public synonym table owner考慮一下下列SQL語句能否在這兩個用戶之間共享SQL能否共享原因select max(sal_cap) from sali mit;不能每個用戶都有一個privatesynonym - sal

7、_limit ,它們是不同的對象，在不同的用戶名下，只能內(nèi)部訪冋select coun t(*0 from work_city能兩個用戶訪問相同的對象where sdescpublic synonym -like NEW%;work_cityselect a.sdesc,b .lo catio n from不能用戶jack通過publicwork_city a ,synonym 訪問pla nt_detail bplant_detail 而 jill 是表where a.city_id = b.city_id的所有者，對象不同SynonymC. 兩個SQL語句中必須使用相同的名字的綁定變量（b

8、ind variables）例如：第一組的兩個SQL語句是相同的（可以共享）,而第二組中的兩個語句是不同的（即使在運行時，賦于不同的綁定變量相同的值）a.select pin ,n ame frompeople wherepin =:blkl.p in;select pin ,n ame frompeople wherepin =:blk1.p in;b.select pin ,n ame frompeople wherepin =:blk1.ot_i nd;select pin ,n ame frompeople wherepin =:blk1.ov_i nd;二.優(yōu)化方法：1. 選擇最有效

9、率的表名順序（只在基于規(guī)則的優(yōu)化器中有效）ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM 子句中寫在最后的表 （基礎表 driving table） 將被最先處理 . 在 FROM 子句中包 含多個表的情況下 ,你必須選擇記錄條數(shù)最少的表作為基礎表 .當 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 EWHER

11、E 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 20002. WHERE 子句中的連接順序ORACLE 采用自下而上 WHERE 子句 ,根據(jù)這個原理 ,表之間的連接 必須寫在 其他 WHERE 條件之前 , 那些可以過濾掉最大數(shù)量記

12、錄的條件必須寫在 WHERE 子句的末尾 .例如:(低效 ,執(zhí)行時間 156.3 秒)SELECT FROM EMP EWHERE SAL 50000AND JOB = MANAGER AND 25 (SELECT COUNT(*) FROM EMP 的順序解析WHERE MGR=E.EMPNO);(高效,執(zhí)行時間 10.6 秒 )SELECT FROM EMP EWHERE 25 50000AND JOB = MANAGER ;3. SELECT子句中避免使用 * 當你想在SELECT子句中列出所有的COLUMN時，使用動態(tài)SQL列引用*是 一個方便的方法.不幸的是，這是一個非常低效的方法實

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

14、LECT 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 , A.SALARY , A.GRADE,B.EMP_NAME , B.SALARY

15、, B.GRADEFROM EMP A,EMP BWHERE A.EMP_NO = 342AND B.EMP_NO = 291;SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 342unionSELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 291;SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 342 or EMP_NO = 291 ;在 SQL*Plus , SQL*Forms 和 Pro*C 中重新設置 A

16、RRAYSIZE 參數(shù) , 可以增加每 次數(shù)據(jù)庫訪問的檢索數(shù)據(jù)量 ,建議值為 200.5. 使用 DECODE 函數(shù)來減少處理時間使用 DECODE 函數(shù)可以避免重復掃描相同記錄或重復連接相同的表 例如:SELECT COUNT(*) ， SUM(SAL)FROM EMPWHERE DEPT_NO = 0020AND ENAME LIKE SMITH% ;SELECT COUNT(*) ， SUM(SAL)FROM EMPWHERE DEPT_NO = 0030AND ENAME LIKE SMITH% ;你可以用 DECODE 函數(shù)高效地得到相同結(jié)果SELECT COUNT(DECODE(D

17、EPT_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ù)也可以運用于 GROUP BY和ORDER BY子句中.6. 整合簡單 , 無關聯(lián)的數(shù)據(jù)庫訪問如果你有幾個簡單的數(shù)據(jù)庫查詢語句 ,你可以把它們整合到一個查詢中 (即使它們 之間

18、沒有關系 )例如:SELECT NAMEFROM EMPWHERE EMP_NO = 1234;SELECT NAMEFROM DPTWHERE DPT_NO = 10 ;SELECT NAMEFROM CATWHERE CAT_TYPE = RD; 上面的 3 個查詢可以被合并成一個 : SELECT E.NAME , D.NAME , C.NAME FROM 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(

19、 X,X.DUMMY) = NVL( X ,C.ROWID(+)AND E.EMP_NO(+) = 1234AND D.DEPT_NO(+) = 10AND C.CAT_TYPE(+) = RD ;(譯者按 : 雖然采取這種方法 ,效率得到提高 ,但是程序的可讀性大大降低 ,所以讀 者 還是要權(quán)衡之間的利弊 )7. 刪除重復記錄最高效的刪除重復記錄方法 （ 因為使用了 ROWID ）DELETE FROM EMP EWHERE E.ROWID (SELECT MIN(X.ROWID)FROM EMP XWHERE X.EMP_NO = E.EMP_NO);8. 用 TRUNCATE 替代 DE

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

21、源而減少 ：COMMIT 所釋放的資源 ：a. 回滾段上用于恢復數(shù)據(jù)的信息.b. 被程序語句獲得的鎖c. redo log buffer 中的空間d. ORACLE 為管理上述 3 種資源中的內(nèi)部花費(譯者按: 在使用 COMMIT 時必須要注意到事務的完整性 ,現(xiàn)實中效率和事務完 整性往往是魚和熊掌不可得兼 )10. 計算記錄條數(shù)和一般的觀點相反 , count(*) 比 count(1) 稍快 , 當然如果可以通過索引 檢索 ,對索引列的計數(shù)仍舊是最快的 . 例如 COUNT(EMPNO)(譯者按：在CSDN論壇中，曾經(jīng)對此有過相當熱烈的討論，作者的觀點并不十 分準確 ,通過實際的測試 ,

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

23、RE REGION REGION != SYDNEYAND REGION != PERTHGROUP BY REGION(譯者按: HAVING 中的條件一般用于對一些集合函數(shù)的比較 ,如 COUNT() 等等.除此而外 ,一般的條件應該寫在 WHERE 子句中 )12. 減少對表的查詢在含有子查詢的 SQL 語句中 ,要特別注意減少對表的查詢 .例如:低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)AND DB_VER= ( SELECT DB_

24、VERFROM TAB_COLUMNSWHERE VERSION = 604)高效SELECT TAB_NAMEFROM TABLES文案大全實用標準文檔WHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHERE VERSION = 604)Update 多個 Column 例子 :低效:UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CA

25、TEGORIES)WHERE EMP_DEPT = 0020;高效:UPDATE EMPSET (EMP_CAT, SAL_RANGE)= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;13 通過內(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.EMPNOAN

26、D 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 = TYP;BEGINOPEN C1;FETCH C1 INTO TDESC;CLOSE C1;RETURN (NVL(TDESC,

27、?);END;FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2 ASENAME 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 HGR

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

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

30、LOC =MELB)這里 oracle 會把 exists 解析 成 join 語句(譯者按：相對來說，用NOT EXISTS替換NOT IN將更顯著地提高效率，下一節(jié)中將指出 )16. 用 NOT EXISTS 替代 NOT IN在子查詢中 ,NOT IN 子句將執(zhí)行一個內(nèi)部的排序和合并 . 無論在哪種情況下,NOT IN都是最低效的(因為它對子查詢中的表執(zhí)行了一個全表遍歷).為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joi ns)或NOT EXISTS.例如：SELECT FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NOFRO

31、M DEPTWHERE 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 XFROM DEPT DWHERE D.DEPT_NO = E.DEPT_NOAND DEPT_CAT = A);17. 用表連接替換 EXISTS通常來說，采用表連接的方式比EXISTS更有效率SELECT

32、ENAMEFROM EMP EWHERE EXISTS (SELECT XFROM DEPTWHERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = A);(更高效 )SELECT ENAMEFROM DEPT D,EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT =A ;(譯者按：在RBO的情況下,前者的執(zhí)行路徑包括FILTER后者使用NESTEDLOOP)18. 用 EXISTS 替換 DISTINCT當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時，避免在SELECT子句中使用 DISTINCT. 一般可以考慮用 EX

33、IST 替換例如 :低效: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);EXISTS 使查詢更為迅速 ,因為 RDBMS 核心模塊將在子查詢的條件一旦滿足 后,立刻返回結(jié)果 .19. 識別低效執(zhí)行的 SQL 語句用下列 SQL 工具找出低效 SQL:SELECT EXECUTIONS , D

34、ISK_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 EXECUTIONS;0ANDBUFFER_GETS ; 0AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS ; list1 SELECT *2 FROM dept, emp3* WHERE emp.deptno = dept.deptnoSQL; set aut

35、otrace 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 3INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)Statistics0 recursive calls2 db block gets30 consistent get

36、s0 physical reads0 redo size2598 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2SQL*Net roundtrips to/from client0sorts (memory)0sorts (disk)14rows processed通過以上分析 ,可以得出實際的執(zhí)行步驟是 :文案大全實用標準文檔1.TABLE ACCESS (FULL) OF EMP2. INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)3. TABLE ACCE

37、SS (BY INDEX ROWID) OF DEPT4. NESTED LOOPS (JOINING 1 AND 3)注 : 目前許多第三方的工具如 TOAD 和 ORACLE 本身提供的工具如 OMS 的 SQL Analyze 都提供了極其方便的 EXPLAIN PLAN 工具.也許喜歡圖形化界面的 朋友們可以選用它們 .22. 用索引提高效率索引是表的一個概念部分,用來提高檢索數(shù)據(jù)的效率實際上ORACLE使用了 一個復雜的自平衡 B-tree 結(jié)構(gòu). 通常,通過索引查詢數(shù)據(jù)比全表掃描要快 . 當 ORACLE 找 出執(zhí)行查詢和 Update 語句的最佳路徑時 , ORACLE 優(yōu)化器將

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

39、標準文檔次的磁盤 I/O . 因為索引需要額外的存儲空間和處理 ,那些不必要的索引反而會 使查詢反應時間變慢 .譯者按 :定期的重構(gòu)索引是有必要的 .ALTER INDEX ; REBUILD ;23. 索引的操作ORACLE 對索引有兩種訪問模式 .索引唯一掃描 ( INDEX UNIQUE SCAN)大多數(shù)情況下 , 優(yōu)化器通過 WHERE 子句訪問 INDEX.例如:表 LODGING 有兩個索引 : 建立在 LODGING 列上的唯一性索引 LODGING_PK 和建立在 MANAGER 列上的非唯一性索引 LODGING$MANAGER.SELECT *FROM LODGINGWHE

40、RE LODGING = ROSE HILL ;在內(nèi)部 , 上述 SQL 將被分成兩步執(zhí)行 , 首先 , LODGING_PK 索引將通過索 引唯一掃描的方式被訪問 , 獲得相對應的 ROWID, 通過 ROWID 訪問表的方式 執(zhí)行下一步檢索 .如果被檢索返回的列包括在INDEX列中ORACLE將不執(zhí)行第二步的處理(通 過 ROWID 訪問表 ). 因為檢索數(shù)據(jù)保存在索引中 , 單單訪問索引就可以完全滿足 查詢結(jié)果 .下面 SQL 只需要 INDEX UNIQUE SCAN 操作.SELECT LODGINGFROM LODGINGWHERE LODGING =ROSE HILL;索引范圍查

41、詢 (INDEX RANGE SCAN)適用于兩種情況 :1. 基于一個范圍的檢索2. 基于非唯一性索引的檢索例 1:SELECT LODGINGFROM LODGINGWHERE LODGING LIKE M% ;WHERE 子句條件包括一系列值 , ORACLE 將通過索引范圍查詢的方式查詢 LODGING_PK . 由于索引范圍查詢將返回一組值 , 它的效率就要比索引唯一掃描低一些 .例 2:SELECT LODGINGFROM LODGINGWHERE MANAGER = BILL GATES ;這個 SQL 的執(zhí)行分兩步 , LODGING$MANAGER 的索引范圍查詢 (得到所有

42、 符合條件記錄的 ROWID) 和下一步同過 ROWID 訪問表得到 LODGING 列的值 . 由于 LODGING$MANAGER 是一個非唯一性的索引 ,數(shù)據(jù)庫不能對它執(zhí)行索引 唯一掃描 .由于SQL返回LODGING列，而它并不存在于LODGING$MANAGER 索引中, 所以在索引范圍查詢后會執(zhí)行一個通過 ROWID 訪問表的操作 .WHERE 子句中 , 如果索引列所對應的值的第一個字符由通配符 (WILDCARD) 開始, 索引將不被采用 .SELECT LODGINGFROM LODGINGWHERE MANAGER LIKE HANMAN ;在這種情況下， ORACLE 將

43、使用全表掃描 .24. 基礎表的選擇基礎表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問).根 據(jù)優(yōu)化器的不同 , SQL 語句中基礎表的選擇是不一樣的 .如果你使用的是 CBO (COST BASED OPTIMIZER),優(yōu)化器會檢查 SQL語句中的 每個表的物理大小 ,索引的狀態(tài) ,然后選用花費最低的執(zhí)行路徑 .如果你用 RBO (RULE BASED OPTIMIZER) , 并且所有的連接條件都有索引對應, 在這種情況下 , 基礎表就是 FROM 子句中列在最后的那個表 .舉例:SELECT A.NAME , B.MANAGERFROM WORKER A

44、,LODGING BWHERE A.LODGING = B.LODING;由于 LODGING 表的 LODING 列上有一個索引 , 而且 WORKER 表中沒有相比 較的索引, WORKER 表將被作為查詢中的基礎表 .25. 多個平等的索引當 SQL 語句的執(zhí)行路徑可以使用分布在多個表上的多個索引時 , ORACLE 會同 時使用多個索引并在運行時對它們的記錄進行合并 , 檢索出僅對全部索引有效的記錄 .在 ORACLE 選擇執(zhí)行路徑時 ,唯一性索引的等級高于非唯一性索引 . 然而這個規(guī) 則只有當 WHERE 子句中 索引列和常量 比較才有效 . 如果索引列和其他表的索引類相比 較 .

45、這種子句在優(yōu)化器中的等級是非常低的 .如果不同表中兩個相同等級的索引將被引用 , FROM 子句中表的順序?qū)Q定哪個 會被率先使用 . FROM 子句中最后的表的索引將有最高的優(yōu)先級 .如果相同表中兩個想同等級的索引將被引用 , WHERE 子句中最先被引用的索引 將有最高的優(yōu)先級 .舉例:DEPTNO 上有一個非唯一性索引 ,EMP_CAT 也有一個非唯一性索引 .SELECT ENAME,FROM EMPWHERE DEPT_NO = 20AND EMP_CAT = A;這里QEPTNO索引將被最先檢索，然后同EMP_CAT索引檢索出的記錄進行合并 執(zhí)行路徑如下 :TABLE ACCESS

46、 BY ROWID ON EMPAND-EQUALINDEX RANGE SCAN ON DEPT_IDXINDEX RANGE SCAN ON CAT_IDX26. 等式比較和范圍比較當WHERE子句中有索引列，ORACLE不能合并它們ORACLE將用范圍比較舉例:DEPTNO 上有一個非唯一性索引 ,EMP_CAT 也有一個非唯一性索引 .SELECT ENAMEFROM EMPWHERE DEPTNO 20AND EMP_CAT =A;這里只有 EMP_CAT 索引被用到 ,然后所有的記錄將逐條與 DEPTNO 條件進 行比較. 執(zhí)行路徑如下 :TABLE ACCESS BY ROWID

47、 ON EMPINDEX RANGE SCAN ON CAT_IDX27. 不明確的索引等級當 ORACLE 無法判斷索引的等級高低差別 ,優(yōu)化器將只使用一個索引 ,它就是在WHERE 子句中被列在最前面的 .舉例:DEPTNO 上有一個非唯一性索引 ,EMP_CAT 也有一個非唯一性索引 .SELECT ENAMEFROM EMPWHERE DEPTNO ; 20AND EMP_CAT ; A ;這里, ORACLE 只用到了 DEPT_NO 索引. 執(zhí)行路徑如下 :TABLE ACCESS BY ROWID ON EMPINDEX RANGE SCAN ON DEPT_IDX譯者按 :我們

48、來試一下以下這種情況 :SQL; select index_name, uniqueness from user_indexes where table_name = EMP;INDEX_NAME UNIQUENESEMPNOUNIQUEEMPTYPENONUNIQUESQL; select * from emp where empno ;= 2 and emp_type = A ;no rows selectedExecution Plan0SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF EMP2 1

49、 INDEX (RANGE SCAN) OF EMPTYPE (NON-UNIQUE)雖然 EMPNO 是唯一性索引 ,但是由于它所做的是范圍比較 , 等級要比非唯一性 索引的等式比較低 !28. 強制索引失效如果兩個或以上索引具有相同的等級 ,你可以強制命令 ORACLE 優(yōu)化器使用其中 的一個(通過它,檢索出的記錄數(shù)量少 ) .舉例:SELECT ENAMEFROM EMPWHERE EMPNO = 7935AND DEPTNO + 0 = 10/*DEPTNO 上的索引將失效 */AND EMP_TYPE | = A /*EMP_TYPE 上的索引將失效 */這是一種相當直接的提高查詢效

50、率的辦法 . 但是你必須謹慎考慮這種策略 ,一般來說,只有在你希望單獨優(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)化器將使用兩個索引并在他們的結(jié)果集合上執(zhí)行排序及 合并.然

51、而，如果其中一個索引(EMP_TYPE)接近于唯一 性而另一個索引(EMP_CLASS)上有幾千個重復的值.排序及合并就會成為一種不必要的負擔 在這種情況下 ,你希望使優(yōu)化器屏蔽掉 EMP_CLASS 索引 .用下面的方案就可以解決問題 .SELECT ENAMEFROM EMPWHERE EMP_TYPE = A AND EMP_CLASS| = X;29. 避免在索引列上使用計算 WHERE 子句中，如果索引列是函數(shù)的一部分優(yōu)化器將不使用索引而使用全表 掃描舉例:低效：SELECT FROM DEPTWHERE SAL * 12 ; 25000;高效:SELECT FROM DEPTWHERE SAL ; 25000/12;譯者按 :這是一個非常實用的規(guī)則，請務必牢記30. 自動選擇索引 如果表中有兩個以上（包括