Oracle 複合索引--轉載

標籤：一個   附加   cat   pre   方式   format   row   操作   rac   

 

 

 

 

索引可以包含一個、兩個或更多個列。兩個或更多個列上的索引被稱作複合索引。複合索引的第一列稱為前置列(leading column)。

轉載自http://yijiangyanyu.iteye.com/blog/1677694

 

索引可以包含一個、兩個或更多個列。兩個或更多個列上的索引被稱作複合索引。複合索引的第一列稱為前置列(leading column)。

B樹索引不儲存索引列全為空白的記錄。對於複合索引，如果某一個索引列不為空白，那麼索引就會包括這條記錄，即使其他所有的所有列都是NULL值。 對於經常查詢欄位IS NULL又希望使用索引的情況，則需要結合查詢條件選擇合適的非空欄位建立複合索引。

 

原則一：首碼性(Prefixing)

 

1、 當使用基於規則的最佳化器（RBO）時，只有 當複合索引的前置列 出現在查詢條件中時，才會 使用到該索引；

2、 Oracle9i之前，使用基於成本的最佳化器（CBO）時， 只有當複合索引的前置列出現在查詢條件中時，才可能會使用到該索引。根據最佳化器估算的使用索引的成本和使用全表掃描的成本，Oracle會自動選擇成本低的訪問路徑；

      例：

SQL> explain plan for select * from  j1 where j1.status=‘125‘ and j1.no=‘NNNN‘;    Explained    SQL> select * from table(dbms_xplan.display);    PLAN_TABLE_OUTPUT  --------------------------------------------------------------------------------  Plan hash value: 581817488  --------------------------------------------------------------------------------  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Tim  --------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                   |   469 |   246K|   113   (1)| 00:  |*  1 |  TABLE ACCESS FULL| J1                |   469 |   246K|   113   (1)| 00:  --------------------------------------------------------------------------------  Predicate Information (identified by operation id):  ---------------------------------------------------     1 - filter("J1"."NO"=‘NNNN‘ AND "J1"."STATUS"=‘125‘)    13 rows selected  

 

   status列是索引的前置列，但是仍使用了全表掃描的方式，因為status欄位上的值為125的行太多了。

3、 從Oracle9i起，Oracle引入了一種新的索引掃描方式——索引跳躍掃描（index skip scan）。索引跳躍掃描基於成本估算，只在使用CBO時可用。這樣，但查詢條件中沒有複合索引的前置列時，最佳化器估算索引跳躍掃描的成本低於其他掃描方式的成本時，就會使用索引；

     查詢條件中不包含前置列，但包含其它的索引列，這種情況下使用索引，通常是查詢條件具有較高的選擇性。

 

   原則二：可選性（Selectivity）

 

Oracle建議複合索引應按欄位可選性（即值的多少）的高低進行排列，這是因為，欄位值越多，可選性越強，定位的記錄就越少，查詢效率就越高。

 

CREATE INDEX name ON employee (emp_lname, emp_fname);  

 

如果第一列 不能單獨提供較高的選擇性 ，複合索引將會非常有用。例如，當許多僱員具有相同的姓氏時，emp_lname 和 emp_fname 上的複合索引非常有用。因為每個僱員都有唯一的 ID，所以 emp_id 和 emp_lname 上的複合索引可能沒有用處，因此列 emp_lname 不會提供任何附加選擇性 。

利用索引中的附加列 ，您可以縮小搜尋的範圍 ，但使用一個具有兩列的索引不同於使用兩個單獨 的索引。複合索引的結構與電話簿類似，它首先按姓氏對僱員 進行排序，然後按名字對所有姓氏相同的僱員進行排序。如果您知道姓氏，電話簿將非常有用，如果您知道名字和姓氏，電話簿則更為有用，但如果您只知道名字而 不知道姓氏，電話簿將沒有用處。

 

索引列順序

 

1、經常搜尋的列排在前面，如僅對一個列多次執行搜尋，則該列應該是複合索引中的第一列。

2、如果對複合索引中的列多次執行單獨的搜尋，則應該在該列上建立另一個單獨的索引。

3、選擇低選擇性列 作為索引列的前置列時，如果查詢條件中包含所有 索引列或者低選擇性列 （前置列），Oracle 選擇的執行計畫中進行“ INDEX RANGE SCAN ”操作，可獲得較好的搜尋效能；如果查詢條件中沒有出現索引前置列 ，而是出現了高選擇性 列。 Oracle 選擇利用索引進行了“ INDEX SKIP SCAN ”操作。

4、選擇高選擇性列為前置列時，如果查詢條件中包含所有 索引列，執行INDEX RANGE SCAN 操作；如果只包含低選擇列，則會執行全表掃描（FTS）；如果包含高選擇性列，則使用INDEX RANGE SCAN 操作。

 

Oracle 複合索引--轉載