Oracle語句優化30個規則詳解

　　 選用適合的Oracle優化器

　　Oracle的優化器共有種

　　a RULE (基於規則)

　　b COST (基於成本)

　　c CHOOSE (選擇性)

　　設置缺省的優化器可以通過對initora文件中OPTIMIZER_MODE參數的各種聲明如RULECOSTCHOOSEALL_ROWSFIRST_ROWS 你當然也在SQL句級或是會話(session)級對其進行覆蓋

　　為了使用基於成本的優化器(CBO CostBased Optimizer) 你必須經常運行analyze 命令以增加數據庫中的對象統計信息(object statistics)的准確性

　　如果數據庫的優化器模式設置為選擇性(CHOOSE)那麼實際的優化器模式將和是否運行過analyze命令有關 如果table已經被analyze過 優化器模式將自動成為CBO 反之數據庫將采用RULE形式的優化器

　　在缺省情況下ORACLE采用CHOOSE優化器為了避免那些不必要的全表掃描(full table scan) 你必須盡量避免使用CHOOSE優化器而直接采用基於規則或者基於成本的優化器

　　 訪問Table的方式Oracle 采用兩種訪問表中記錄的方式

　　a 全表掃描

　　全表掃描就是順序地訪問表中每條記錄 ORACLE采用一次讀入多個數據塊(database block)的方式優化全表掃描

　　b 通過ROWID訪問表

　　你可以采用基於ROWID的訪問方式情況提高訪問表的效率 ROWID包含了表中記錄的物理位置信息……ORACLE采用索引(INDEX)實現了數據和存放數據的物理位置(ROWID)之間的聯系通常索引提供了快速訪問ROWID的方法因此那些基於索引列的查詢就可以得到性能上的提高

　　 共享SQL語句

　　為了不重復解析相同的SQL語句在第一次解析之後 ORACLE將SQL語句存放在內存中這塊位於系統全局區域SGA(system global area)的共享池(shared buffer pool)中的內存可以被所有的數據庫用戶共享 因此當你執行一個SQL語句(有時被稱為一個游標)時如果它和之前的執行過的語句完全相同 ORACLE就能很快獲得已經被解析的語句以及最好的執行路徑 ORACLE的這個功能大大地提高了SQL的執行性能並節省了內存的使用

　　可惜的是ORACLE只對簡單的表提供高速緩沖(cache buffering) 這個功能並不適用於多表連接查詢

　　數據庫管理員必須在initora中為這個區域設置合適的參數當這個內存區域越大就可以保留更多的語句當然被共享的可能性也就越大了

　　當你向ORACLE 提交一個SQL語句ORACLE會首先在這塊內存中查找相同的語句

　　這裡需要注明的是ORACLE對兩者采取的是一種嚴格匹配要達成共享SQL語句必須完全相同(包括空格換行等)

　　共享的語句必須滿足三個條件

　　A 字符級的比較

　　當前被執行的語句和共享池中的語句必須完全相同

　　例如

　　 　SELECT * FROM EMP;

　　和下列每一個都不同

　　       SELECT * from EMP;
　　Select * From Emp;
　　SELECT * FROM EMP;

　　B 兩個語句所指的對象必須完全相同

　　例如

　　用戶 對象名 如何訪問

　　    　Jack sal_limit private synonym
　　Work_city public synonym
　　Plant_detail public synonym
　　Jill sal_limit private synonym
　　Work_city public synonym
　　Plant_detail table owner

　　考慮一下下列SQL語句能否在這兩個用戶之間共享

　　[NextPage]

　　SQL 能否共享 原因

　　select max(sal_cap) from sal_limit; 不能 每個用戶都有一個private synonym sal_limit 它們是不同的對象

　　select count(* from work_city where sdesc like NEW%; 能 兩個用戶訪問相同的對象public synonym work_city

　　select asdescblocation from work_city a plant_detail b where acity_id = bcity_id 不能 用戶jack 通過private synonym訪問plant_detail 而jill 是表的所有者對象不同

　　C 兩個SQL語句中必須使用相同的名字的綁定變量(bind variables)

　　例如第一組的兩個SQL語句是相同的(可以共享)而第二組中的兩個語句是不同的(即使在運行時賦於不同的綁定變量相同的值)

　　a　

　　 select pin  name from people where pin = blkpin;
select pin  name from people where pin = blkpin;

　　b

　　 select pin  name from people where pin = blkot_ind;
select pin  name from people where pin = blkov_ind;

　　 選擇最有效率的表名順序(只在基於規則的優化器中有效)

　　ORACLE的解析器按照從右到左的順序處理FROM子句中的表名因此FROM子句中寫在最後的表(基礎表 driving table)將被最先處理 在FROM子句中包含多個表的情況下你必須選擇記錄條數最少的表作為基礎表當ORACLE處理多個表時會運用排序及合並的方式連接它們首先掃描第一個表(FROM子句中最後的那個表)並對記錄進行派序然後掃描第二個表(FROM子句中最後第二個表)最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合並

　　例如

　　表 TAB 條記錄

　　表 TAB 條記錄

　　選擇TAB作為基礎表 (最好的方法)

　　 select count(*) from tabtab 執行時間秒

　　選擇TAB作為基礎表 (不佳的方法)　

　　 select count(*) from tabtab 執行時間秒

　　如果有個以上的表連接查詢 那就需要選擇交叉表(intersection table)作為基礎表 交叉表是指那個被其他表所引用的表

　　例如 EMP表描述了LOCATION表和CATEGORY表的交集

　　   　SELECT *
　　FROM LOCATION L 
　　CATEGORY C
　　EMP E
　　WHERE EEMP_NO BETWEEN  AND 
　　AND ECAT_NO = CCAT_NO
　　AND ELOCN = LLOCN

　　將比下列SQL更有效率

　　    　SELECT *
　　FROM EMP E 
　　LOCATION L 
　　CATEGORY C
　　WHERE ECAT_NO = CCAT_NO
　　AND ELOCN = LLOCN
　　AND EEMP_NO BETWEEN  AND 

　　[NextPage]

　　 WHERE子句中的連接順序

　　ORACLE采用自下而上的順序解析WHERE子句根據這個原理表之間的連接必須寫在其他WHERE條件之前 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾

　　例如

　　（低效執行時間秒）

　　   　SELECT …
　　FROM EMP E
　　WHERE SAL > 
　　AND JOB = MANAGER
　　AND  < (SELECT COUNT(*) FROM EMP
　　WHERE MGR=EEMPNO);

　　(高效執行時間秒)

　　       SELECT …
　　FROM EMP E
　　WHERE  < (SELECT COUNT(*) FROM EMP
　　WHERE MGR=EEMPNO)
　　AND SAL > 
　　AND JOB = MANAGER;

　　 SELECT子句中避免使用 *

　　當你想在SELECT子句中列出所有的COLUMN時使用動態SQL列引用 * 是一個方便的方法不幸的是這是一個非常低效的方法實際上ORACLE在解析的過程中 會將* 依次轉換成所有的列名 這個工作是通過查詢數據字典完成的 這意味著將耗費更多的時間

　　 減少訪問數據庫的次數

　　當執行每條SQL語句時 ORACLE在內部執行了許多工作 解析SQL語句 估算索引的利用率 綁定變量 讀數據塊等等 由此可見 減少訪問數據庫的次數 就能實際上減少ORACLE的工作量

　　例如以下有三種方法可以檢索出雇員號等於或的職員

　　方法 (最低效)

　　       SELECT EMP_NAME  SALARY  GRADE
　　FROM EMP
　　WHERE EMP_NO = ;
　　SELECT EMP_NAME  SALARY  GRADE
　　FROM EMP
　　WHERE EMP_NO = ;

　　方法 (次低效)

　　

　　DECLARE
　　CURSOR C (E_NO NUMBER) IS
　　SELECT EMP_NAMESALARYGRADE
　　FROM EMP
　　WHERE EMP_NO = E_NO;
　  BEGIN
　　OPEN C();
　　FETCH C INTO … ;
　　OPEN C();
　　FETCH C INTO … ;
　　CLOSE C; END;

　　方法 (高效)

　　       SELECT AEMP_NAME  ASALARY  AGRADE
　　BEMP_NAME  BSALARY  BGRADE
　　FROM EMP AEMP B
　　WHERE AEMP_NO = 
　　AND BEMP_NO = ;

　　注意

　　在SQL*Plus SQL*Forms和Pro*C中重新設置ARRAYSIZE參數 可以增加每次數據庫訪問的檢索數據量 建議值為

　　[NextPage]

　　 使用DECODE函數來減少處理時間

　　使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表

　　例如

　　      SELECT COUNT(*)SUM(SAL)
　　FROM　EMP
　　WHERE DEPT_NO = 
　　AND ENAME LIKE　SMITH%;
　　SELECT COUNT(*)SUM(SAL)
　　FROM　EMP
　　WHERE DEPT_NO = 
　　AND ENAME LIKE　SMITH%;

　　你可以用DECODE函數高效地得到相同結果

　　   　SELECT COUNT(DECODE(DEPT_NOXNULL)) D_COUNT
　　COUNT(DECODE(DEPT_NOXNULL)) D_COUNT
　　SUM(DECODE(DEPT_NOSALNULL)) D_SAL
　　SUM(DECODE(DEPT_NOSALNULL)) D_SAL
　　FROM EMP WHERE ENAME LIKE SMITH%;

　　類似的DECODE函數也可以運用於GROUP BY 和ORDER BY子句中

　　 整合簡單無關聯的數據庫訪問

　　如果你有幾個簡單的數據庫查詢語句你可以把它們整合到一個查詢中(即使它們之間沒有關系)

　　例如

　　       SELECT NAME
　　FROM EMP
　　WHERE EMP_NO = ;
　　SELECT NAME
　　FROM DPT
　　WHERE DPT_NO =  ;
　　SELECT NAME
　　FROM CAT
　　WHERE CAT_TYPE = RD;

　　上面的個查詢可以被合並成一個

　　      SELECT ENAME  DNAME  CNAME
　　FROM CAT C  DPT D  EMP EDUAL X
　　WHERE NVL(XXDUMMY) = NVL(XEROWID(+))
　　AND NVL(XXDUMMY) = NVL(XDROWID(+))
　　AND NVL(XXDUMMY) = NVL(XCROWID(+))
　　AND EEMP_NO(+) = 
　　AND DDEPT_NO(+) = 
　　AND CCAT_TYPE(+) = RD;

　　(譯者按 雖然采取這種方法效率得到提高但是程序的可讀性大大降低所以讀者還是要權衡之間的利弊)

　　[NextPage]

　　 刪除重復記錄

　　最高效的刪除重復記錄方法 ( 因為使用了ROWID)

　　    　DELETE FROM EMP E
　　WHERE EROWID > (SELECT MIN(XROWID)
　　FROM EMP X
　　WHERE XEMP_NO = EEMP_NO);

　　 用TRUNCATE替代DELETE

　　當刪除表中的記錄時在通常情況下 回滾段(rollback segments ) 用來存放可以被恢復的信息 如果你沒有COMMIT事務ORACLE會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況)

　　而當運用TRUNCATE時 回滾段不再存放任何可被恢復的信息當命令運行後數據不能被恢復因此很少的資源被調用執行時間也會很短

　　(譯者按 TRUNCATE只在刪除全表適用TRUNCATE是DDL不是DML)

　　 盡量多使用COMMIT

　　只要有可能在程序中盡量多使用COMMIT 這樣程序的性能得到提高需求也會因為COMMIT所釋放的資源而減少COMMIT所釋放的資源

　　a 回滾段上用於恢復數據的信息

　　b 被程序語句獲得的鎖

　　c redo log buffer 中的空間

　　d ORACLE為管理上述種資源中的內部花費

　　(譯者按 在使用COMMIT時必須要注意到事務的完整性現實中效率和事務完整性往往是魚和熊掌不可得兼)

　　 計算記錄條數

　　和一般的觀點相反 count(*) 比count()稍快 當然如果可以通過索引檢索對索引列的計數仍舊是最快的 例如 COUNT(EMPNO)

　　(譯者按 在CSDN論壇中曾經對此有過相當熱烈的討論 作者的觀點並不十分准確通過實際的測試上述三種方法並沒有顯著的性能差別)

　　 用Where子句替換HAVING子句

　　避免使用HAVING子句 HAVING 只會在檢索出所有記錄之後才對結果集進行過濾 這個處理需要排序總計等操作 如果能通過WHERE子句限制記錄的數目那就能減少這方面的開銷

　　例如

　　低效

　　
　SELECT REGIONAVG(LOG_SIZE)
　　FROM LOCATION
　　GROUP BY REGION
　　HAVING REGION REGION != SYDNEY
　　AND REGION != PERTH

　　高效

　　       SELECT REGIONAVG(LOG_SIZE)
　　FROM LOCATION
　　WHERE REGION REGION != SYDNEY
　　AND REGION != PERTH
　　GROUP BY REGION

　　(譯者按 HAVING 中的條件一般用於對一些集合函數的比較如COUNT() 等等 除此而外一般的條件應該寫在WHERE子句中)

　　[NextPage]

　　 減少對表的查詢

　　在含有子查詢的SQL語句中要特別注意減少對表的查詢

　　例如

　　低效

　　       SELECT TAB_NAME
　　FROM TABLES
　　WHERE TAB_NAME = ( SELECT TAB_NAME
　　FROM TAB_COLUMNS
　　WHERE VERSION = )
　　AND　DB_VER= ( SELECT DB_VER
　　FROM TAB_COLUMNS
　　WHERE VERSION = )

　　高效

　　       SELECT TAB_NAME
　　FROM TABLES
　　WHERE (TAB_NAMEDB_VER)
　　= ( SELECT TAB_NAMEDB_VER)
　　FROM TAB_COLUMNS
　　WHERE VERSION = )
　　Update 多個Column 例子

　　低效

　　       UPDATE EMP
　　SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES)
　　SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
　　WHERE EMP_DEPT = ;

　　高效

　　    　UPDATE EMP
　　SET (EMP_CAT SAL_RANGE)
　　= (SELECT MAX(CATEGORY)  MAX(SAL_RANGE)
　　FROM EMP_CATEGORIES)
　　WHERE EMP_DEPT = ;

　　 通過內部函數提高SQL效率

　　       SELECT HEMPNOEENAMEHHIST_TYPETTYPE_DESCCOUNT(*)
　　FROM HISTORY_TYPE TEMP EEMP_HISTORY H
　　WHERE HEMPNO = EEMPNO
　　AND HHIST_TYPE = THIST_TYPE
　　GROUP BY HEMPNOEENAMEHHIST_TYPETTYPE_DESC;

　　通過調用下面的函數可以提高效率

　　       FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR
　　AS
　　TDESC VARCHAR();
　　CURSOR C IS
　　SELECT TYPE_DESC
　　FROM HISTORY_TYPE
　　WHERE HIST_TYPE = TYP;
　　BEGIN
　　OPEN C;
　　FETCH C INTO TDESC;
　　CLOSE C;
　　RETURN (NVL(TDESC?));
　　END;
　　FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR
　　AS
　　ENAME VARCHAR();
　　CURSOR C IS
　　SELECT ENAME
　　FROM EMP
　　WHERE EMPNO=EMP;
　　BEGIN
　　OPEN C;
　　FETCH C INTO ENAME;
　　CLOSE C;
　　RETURN (NVL(ENAME?));
　　END;
　　SELECT HEMPNOLOOKUP_EMP(HEMPNO)
　　HHIST_TYPELOOKUP_HIST_TYPE(HHIST_TYPE)COUNT(*)
　　FROM EMP_HISTORY H
　　GROUP BY HEMPNO  HHIST_TYPE;

　　(譯者按 經常在論壇中看到如 能不能用一個SQL寫出… 的貼子 殊不知復雜的SQL往往犧牲了執行效率 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的)

　　[NextPage]

　　 使用表的別名(Alias)

　　當在SQL語句中連接多個表時 請使用表的別名並把別名前綴於每個Column上這樣一來就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤

　　(譯者注 Column歧義指的是由於SQL中不同的表具有相同的Column名當SQL語句中出現這個Column時SQL解析器無法判斷這個Column的歸屬)

　　 用EXISTS替代IN

　　在許多基於基礎表的查詢中為了滿足一個條件往往需要對另一個表進行聯接在這種情況下 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率

　　低效

　　       SELECT *
　　FROM EMP (基礎表)
　　WHERE EMPNO > 
　　AND DEPTNO IN (SELECT DEPTNO
　　FROM DEPT
　　WHERE LOC = MELB)

　　高效

　　       SELECT *
　　FROM EMP (基礎表)
　　WHERE EMPNO > 
　　AND EXISTS (SELECT X
　　FROM DEPT
　　WHERE DEPTDEPTNO = EMPDEPTNO
　　AND LOC = MELB)

　　(譯者按 相對來說用NOT EXISTS替換NOT IN 將更顯著地提高效率下一節中將指出)

　　 用NOT EXISTS替代NOT IN

　　在子查詢中NOT IN子句將執行一個內部的排序和合並 無論在哪種情況下NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷) 為了避免使用NOT IN 我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS

　　例如

　　      SELECT …
　　FROM EMP
　　WHERE DEPT_NO NOT IN (SELECT DEPT_NO
　　FROM DEPT
　　WHERE DEPT_CAT=A);

　　為了提高效率改寫為

　　(方法一 高效)

　　    　SELECT …
　　FROM EMP ADEPT B
　　WHERE ADEPT_NO = BDEPT(+)
　　AND BDEPT_NO IS NULL
　　AND BDEPT_CAT(+) = A

　　(方法二 最高效)

　　       SELECT …
　　FROM EMP E
　　WHERE NOT EXISTS (SELECT X
　　FROM DEPT D
　　WHERE DDEPT_NO = EDEPT_NO
　　AND DEPT_CAT = A);

　　 用表連接替換EXISTS

　　通常來說 采用表連接的方式比EXISTS更有效率

　　   　SELECT ENAME
　　FROM EMP E
　　WHERE EXISTS (SELECT X
　　FROM DEPT
　　WHERE DEPT_NO = EDEPT_NO
　　AND DEPT_CAT = A);

　　(更高效)

　　       SELECT ENAME
　　FROM DEPT DEMP E
　　WHERE EDEPT_NO = DDEPT_NO
　　AND DEPT_CAT = A ;

　　(譯者按 在RBO的情況下前者的執行路徑包括FILTER後者使用NESTED LOOP)

　　[NextPage]

　　 用EXISTS替換DISTINCT

　　當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時避免在SELECT子句中使用DISTINCT 一般可以考慮用EXIST替換

　　例如

　　低效

　　      SELECT DISTINCT DEPT_NODEPT_NAME
　　FROM DEPT DEMP E
　　WHERE DDEPT_NO = EDEPT_NO

　　高效

　　   　SELECT DEPT_NODEPT_NAME
　　FROM DEPT D
　　WHERE EXISTS ( SELECT X
　　FROM EMP E
　　WHERE EDEPT_NO = DDEPT_NO);

　　EXISTS 使查詢更為迅速因為RDBMS核心模塊將在子查詢的條件一旦滿足後立刻返回結果

　　 識別低效執行的SQL語句

　　用下列SQL工具找出低效SQL

　　   　SELECT EXECUTIONS  DISK_READS BUFFER_GETS
　　ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS) Hit_radio
　　ROUND(DISK_READS/EXECUTIONS) Reads_per_run
　　SQL_TEXT
　　FROM V$SQLAREA
　　WHERE EXECUTIONS>
　　AND BUFFER_GETS > 
　　AND (BUFFER_GETSDISK_READS)/BUFFER_GETS < 
　　ORDER BY  DESC;

　　(譯者按 雖然目前各種關於SQL優化的圖形化工具層出不窮但是寫出自己的SQL工具來解決問題始終是一個最好的方法)

　　 使用TKPROF 工具來查詢SQL性能狀態

　　SQL trace 工具收集正在執行的SQL的性能狀態數據並記錄到一個跟蹤文件中 這個跟蹤文件提供了許多有用的信息例如解析次數執行次數CPU使用時間等這些數據將可以用來優化你的系統

　　設置SQL TRACE在會話級別

　　有效

　　ALTER SESSION SET SQL_TRACE TRUE

　　設置SQL TRACE 在整個數據庫有效仿 你必須將SQL_TRACE參數在initora中設為TRUE USER_DUMP_DEST參數說明了生成跟蹤文件的目錄

　　(譯者按 這一節中作者並沒有提到TKPROF的用法 對SQL TRACE的用法也不夠准確 設置SQL TRACE首先要在initora中設定TIMED_STATISTICS 這樣才能得到那些重要的時間狀態生成的trace文件是不可讀的所以要用TKPROF工具對其進行轉換TKPROF有許多執行參數大家可以參考ORACLE手冊來了解具體的配置 )

　　 用EXPLAIN PLAN 分析SQL語句

　　EXPLAIN PLAN 是一個很好的分析SQL語句的工具它甚至可以在不執行SQL的情況下分析語句 通過分析我們就可以知道ORACLE是怎麼樣連接表使用什麼方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱

　　你需要按照從裡到外從上到下的次序解讀分析的結果 EXPLAIN PLAN分析的結果是用縮進的格式排列的 最內部的操作將被最先解讀 如果兩個操作處於同一層中帶有最小操作號的將被首先執行

　　NESTED LOOP是少數不按照上述規則處理的操作 正確的執行路徑是檢查對NESTED LOOP提供數據的操作其中操作號最小的將被最先處理

　　譯者按通過實踐 感到還是用SQLPLUS中的SET TRACE 功能比較方便

　　舉例

　　   　SQL> list
　　 SELECT *
　　 FROM dept emp
　　* WHERE empdeptno = deptdeptno
　　SQL> set autotrace traceonly /*traceonly 可以不顯示執行結果*/
　　SQL> /
　　 rows selected
　　Execution Plan
　　
　　 SELECT STATEMENT ptimizer=CHOOSE
　　  NESTED LOOPS
　　  TABLE ACCESS (FULL) OF EMP
　　  TABLE ACCESS (BY INDEX ROWID) OF DEPT
　　  INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)
　　Statistics
　　
　　 recursive calls
　　 db block gets
　　 consistent gets
　　 physical reads
　　 redo size
　　 bytes sent via SQL*Net to client
　　 bytes received via SQL*Net from client
　　 SQL*Net roundtrips to/from client
　　 sorts (memory)
　　 sorts (disk)
　　 rows processed

　　通過以上分析可以得出實際的執行步驟是

　　    　 TABLE ACCESS (FULL) OF EMP
　　 INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)
　　 TABLE ACCESS (BY INDEX ROWID) OF DEPT
　　 NESTED LOOPS (JOINING  AND )

　　注 目前許多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了極其方便的EXPLAIN PLAN工具也許喜歡圖形化界面的朋友們可以選用它們

　　[NextPage]

　　 用索引提高效率

　　索引是表的一個概念部分用來提高檢索數據的效率 實際上ORACLE使用了一個復雜的自平衡Btree結構通常通過索引查詢數據比全表掃描要快 當ORACLE找出執行查詢和Update語句的最佳路徑時 ORACLE優化器將使用索引同樣在聯結多個表時使用索引也可以提高效率 另一個使用索引的好處是它提供了主鍵(primary key)的唯一性驗證

　　除了那些LONG或LONG RAW數據類型 你可以索引幾乎所有的列 通常 在大型表中使用索引特別有效 當然你也會發現 在掃描小表時使用索引同樣能提高效率

　　雖然使用索引能得到查詢效率的提高但是我們也必須注意到它的代價 索引需要空間來存儲也需要定期維護每當有記錄在表中增減或索引列被修改時 索引本身也會被修改 這意味著每條記錄的INSERT DELETE UPDATE將為此多付出 次的磁盤I/O 因為索引需要額外的存儲空間和處理那些不必要的索引反而會使查詢反應時間變慢

　　譯者按定期的重構索引是有必要的

　　ALTER INDEX REBUILD

　　 索引的操作

　　ORACLE對索引有兩種訪問模式

　　索引唯一掃描 ( INDEX UNIQUE SCAN)

　　大多數情況下 優化器通過WHERE子句訪問INDEX

　　例如

　　表LODGING有兩個索引 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER

　　       SELECT *
　　FROM LODGING
　　WHERE LODGING = ROSE HILL;

　　在內部 上述SQL將被分成兩步執行 首先 LODGING_PK 索引將通過索引唯一掃描的方式被訪問 獲得相對應的ROWID 通過ROWID訪問表的方式執行下一步檢索

　　如果被檢索返回的列包括在INDEX列中ORACLE將不執行第二步的處理(通過ROWID訪問表) 因為檢索數據保存在索引中 單單訪問索引就可以完全滿足查詢結果

　　下面SQL只需要INDEX UNIQUE SCAN 操作

　　      SELECT LODGING
　　FROM LODGING
　　WHERE LODGING = ROSE HILL;

　　索引范圍查詢(INDEX RANGE SCAN)

　　適用於兩種情況

　　 基於一個范圍的檢索

　　 基於非唯一性索引的檢索

　　例

　　      SELECT LODGING FROM LODGING WHERE LODGING LIKE M%;

　　WHERE子句條件包括一系列值 ORACLE將通過索引范圍查詢的方式查詢LODGING_PK 由於索引范圍查詢將返回一組值 它的效率就要比索引唯一掃描低一些

　　例　

　　      SELECT LODGING
　　FROM LODGING
　　WHERE MANAGER = BILL GATES;

　　這個SQL的執行分兩步 LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID)和下一步同過ROWID訪問表得到LODGING列的值 由於LODGING$MANAGER是一個非唯一性的索引數據庫不能對它執行索引唯一掃描

　　由於SQL返回LODGING列而它並不存在於LODGING$MANAGER索引中 所以在索引范圍查詢後會執行一個通過ROWID訪問表的操作

　　WHERE子句中 如果索引列所對應的值的第一個字符由通配符(WILDCARD)開始 索引將不被采用在這種情況下ORACLE將使用全表掃描

　　    　SELECT LODGING
　　FROM LODGING
　　WHERE MANAGER LIKE %HANMAN;

　　 基礎表的選擇

　　基礎表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問) 根據優化器的不同 SQL語句中基礎表的選擇是不一樣的

　　如果你使用的是CBO (COST BASED OPTIMIZER)優化器會檢查SQL語句中的每個表的物理大小索引的狀態然後選用花費最低的執行路徑

　　如果你用RBO (RULE BASED OPTIMIZER) 並且所有的連接條件都有索引對應 在這種情況下 基礎表就是FROM 子句中列在最後的那個表blog

　　舉例

　　   　SELECT ANAME  BMANAGER
　　FROM　WORKER A
　　LODGING B
　　WHERE　ALODGING = BLODING;

　　由於LODGING表的LODING列上有一個索引 而且WORKER表中沒有相比較的索引 WORKER表將被作為查詢中的基礎表

　　[NextPage]

　　 多個平等的索引

　　當SQL語句的執行路徑可以使用分布在多個表上的多個索引時 ORACLE會同時使用多個索引並在運行時對它們的記錄進行合並 檢索出僅對全部索引有效的記錄

　　在ORACLE選擇執行路徑時唯一性索引的等級高於非唯一性索引 然而這個規則只有當WHERE子句中索引列和常量比較才有效如果索引列和其他表的索引類相比較 這種子句在優化器中的等級是非常低的

　　如果不同表中兩個想同等級的索引將被引用 FROM子句中表的順序將決定哪個會被率先使用 FROM子句中最後的表的索引將有最高的優先級

　　如果相同表中兩個想同等級的索引將被引用 WHERE子句中最先被引用的索引將有最高的優先級

　　舉例

　　DEPTNO上有一個非唯一性索引EMP_CAT也有一個非唯一性索引

　　      SELECT ENAME
　　FROM EMP
　　WHERE DEPT_NO = 
　　AND EMP_CAT = A;

　　這裡DEPTNO索引將被最先檢索然後同EMP_CAT索引檢索出的記錄進行合並 執行路徑如下　

　　      TABLE ACCESS BY ROWID ON EMP
　　ANDEQUAL
　　INDEX RANGE SCAN ON DEPT_IDX
　　INDEX RANGE SCAN ON CAT_IDX

　　 等式比較和范圍比較

　　當WHERE子句中有索引列 ORACLE不能合並它們ORACLE將用范圍比較

　　舉例

　　DEPTNO上有一個非唯一性索引EMP_CAT也有一個非唯一性索引

　　       SELECT ENAME
　　FROM EMP
　　WHERE DEPTNO > 
　　AND EMP_CAT = A;

　　這裡只有EMP_CAT索引被用到然後所有的記錄將逐條與DEPTNO條件進行比較 執行路徑如下　　

　　      TABLE ACCESS BY ROWID ON EMP
　　INDEX RANGE SCAN ON CAT_IDX

　　 不明確的索引等級

　　當ORACLE無法判斷索引的等級高低差別優化器將只使用一個索引它就是在WHERE子句中被列在最前面的

　　舉例

　　DEPTNO上有一個非唯一性索引EMP_CAT也有一個非唯一性索引

　　       SELECT ENAME
　　FROM EMP
　　WHERE DEPTNO > 
　　AND EMP_CAT > A;

　　這裡 ORACLE只用到了DEPT_NO索引 執行路徑如下

　　       TABLE ACCESS BY ROWID ON EMP
　　INDEX RANGE SCAN ON DEPT_IDX

　　譯者按我們來試一下以下這種情況

　　   　SQL> select index_name uniqueness from user_indexes where table_name = EMP;
　　INDEX_NAME UNIQUENES
　　 
　　EMPNO UNIQUE
　　EMPTYPE NONUNIQUE
　　SQL> select * from emp where empno >=  and emp_type = A ;
　　no rows selected
　　Execution Plan
　　
　　 SELECT STATEMENT ptimizer=CHOOSE
　　  TABLE ACCESS (BY INDEX ROWID) OF EMP
　　  INDEX (RANGE SCAN) OF EMPTYPE (NONUNIQUE)

　　雖然EMPNO是唯一性索引但是由於它所做的是范圍比較 等級要比非唯一性索引的等式比較低!

From:http://tw.wingwit.com/Article/program/Oracle/201311/17830.html