ORACLE索引提高效率

　　用索引提高效率

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

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

　　雖然使用索引能得到查詢效率的提高但是我們也必須注意到它的代價 索引需要空間來

　　存儲也需要定期維護 每當有記錄在表中增減或索引列被修改時 索引本身也會被修改 這意味著每條記錄的INSERT DELETE UPDATE將為此多付出 次的磁盤I/O 因為索引需要額外的存儲空間和處理那些不必要的索引反而會使查詢反應時間變慢

　　定期的重構索引是有必要的

　　ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

　　索引的操作

　　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)開始 索引將不被采用

　　SELECT LODGING FROM LODGING WHERE MANAGER LIKE ％HANMAN;

　　在這種情況下ORACLE將使用全表掃描

　　基礎表的選擇

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

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

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

　　舉例:

　　SELECT ANAME BMANAGER FROM　WORKER A LODGING B

　　WHERE　ALODGING = BLODING;

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

　　多個平等的索引

　　當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 Optimizer=CHOOSE

　　 TABLE ACCESS (BY INDEX ROWID) OF EMP

　　 INDEX (RANGE SCAN) OF EMPTYPE (NONUNIQUE)

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

　　強制索引失效

　　如果兩個或以上索引具有相同的等級你可以強制命令ORACLE優化器使用其中的一個(通過它檢索出的記錄數量少)

　　舉例:

　　SELECT ENAME FROM EMP WHERE EMPNO =

　　AND DEPTNO + = /*DEPTNO上的索引將失效*/

　　AND EMP_TYPE || = A /*EMP_TYPE上的索引將失效*/

　　這是一種相當直接的提高查詢效率的辦法 但是你必須謹慎考慮這種策略一般來說只有在你希望單獨優化幾個SQL時才能采用它

　　這裡有一個例子關於何時采用這種策略

　　假設在EMP表的EMP_TYPE列上有一個非唯一性的索引而EMP_CLASS上沒有索引

　　SELECT ENAME FROM EMP WHERE EMP_TYPE = A AND EMP_CLASS = X;

　　優化器會注意到EMP_TYPE上的索引並使用它 這是目前唯一的選擇 如果一段時間以後 另一個非唯一性建立在EMP_CLASS上優化器必須對兩個索引進行選擇在通常情況下優化器將使用兩個索引並在他們的結果集合上執行排序及合並 然而如果其中一個索引（EMP_TYPE）接近於唯一性而另一個索引（EMP_CLASS）上有幾千個重復的值 排序及合並就會成為一種不必要的負擔 在這種情況下你希望使優化器屏蔽掉EMP_CLASS索引

　　用下面的方案就可以解決問題

　　SELECT ENAME FROM EMP WHERE EMP_TYPE = A AND EMP_CLASS|| = X;

　　避免在索引列上使用計算．

　　WHERE子句中如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描．

　　舉例:

　　低效

　　SELECT … FROM DEPT WHERE SAL * > ;

　　高效:

　　SELECT … FROM DEPT WHERE SAL > /;

　　自動選擇索引

　　如果表中有兩個以上（包括兩個）索引其中有一個唯一性索引而其他是非唯一性．

　　在這種情況下ORACLE將使用唯一性索引而完全忽略非唯一性索引．

　　舉例:

　　SELECT ENAME FROM EMP WHERE EMPNO =

　　AND DEPTNO = ;

　　這裡只有EMPNO上的索引是唯一性的所以EMPNO索引將用來檢索記錄．

　　TABLE ACCESS BY ROWID ON EMP

　　INDEX UNIQUE SCAN ON EMP_NO_IDX

　　避免在索引列上使用NOT

　　通常　我們要避免在索引列上使用NOT NOT會產生在和在索引列上使用函數相同的

　　影響 當ORACLE遇到NOT他就會停止使用索引轉而執行全表掃描

　　舉例:

　　低效: (這裡不使用索引)

　　SELECT … FROM DEPT WHERE DEPT_CODE NOT = ;

　　高效: (這裡使用了索引)

　　SELECT … FROM DEPT WHERE DEPT_CODE > ;

　　需要注意的是在某些時候 ORACLE優化器會自動將NOT轉化成相對應的關系操作符

　　NOT > to <=

　　NOT >= to <

　　NOT < to >=

　　NOT <= to >

　　SQL> select * from emp where NOT empno > ;

　　no rows selected

　　Execution Plan

　　

　　 SELECT STATEMENT Optimizer=CHOOSE

　　 TABLE ACCESS (BY INDEX ROWID) OF EMP

　　 INDEX (RANGE SCAN) OF EMPNO (UNIQUE)

　　SQL> select * from emp where empno <= ;

　　no rows selected

　　Execution Plan

　　

　　 SELECT STATEMENT Optimizer=CHOOSE

　　 TABLE ACCESS (BY INDEX ROWID) OF EMP

　　 INDEX (RANGE SCAN) OF EMPNO (UNIQUE)

　　兩者的效率完全一樣也許這符合作者關於 在某些時候 ORACLE優化器會自動將NOT轉化成相對應的關系操作符 的觀點．

　　用>=替代>

　　如果DEPTNO上有一個索引

　　高效:

　　SELECT * FROM EMP WHERE DEPTNO >=

　　低效:

　　SELECT * FROM EMP WHERE DEPTNO >

　　兩者的區別在於 前者DBMS將直接跳到第一個DEPT等於的記錄而後者將首先定位到DEPTNO=的記錄並且向前掃描到第一個DEPT大於的記錄

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