Expert 101 Oracle——索引

    一、索引應該是開發設計時就應該考慮的，而不是系統上線後才補充的。尤其是開發時瞭解應用的前提下，知道資料的流程，建立適當的索引，這對效能影響是至關重要的。

[@more@]

    二、索引的種類：

      1、  B*tree索引：同二叉樹結構相似，是主要的索引型別。包括以下幾種型別

（1、）索引組織表：以索引結構作為儲存方式的表。

（2、）B*tree聚簇索引：聚簇中用於儲存聚簇列的索引。

                  （3、）反向鍵索引：索引列值以反向的形式儲存，以減少快速插入時對同一個索引塊的爭用。

（4、）降序索引：同一般的升序索引不同，其索引值以降序形式儲存。

      2、  點陣圖索引：一個單獨的索引條目用點陣圖指向不同的行。適用於低基數列且DML操作較少的表（只讀），OLTP較少用，適用於DSS系統。

      3、  函式索引：B*tree或點陣圖索引用於儲存索引列函式計算後的值。

      4、  應用程式域索引：自我建立和儲存（ORACLE內部或外部）的一種索引。可以自己  決定索引的選擇性和成本，以使最佳化器是否決定運用。

      5、  interMedia Text索引：ORACLE特定的一種用於大文字關鍵字檢索的索引。

三、B*tree索引：

      1、  非唯一索引：非唯一索引儲存時事實上是唯一的，是因為ORACLE先會按列值進行排序，接下按ROWID排序進行儲存，由於不同行的ROWID不同，因此索引儲存時將會是唯一值。（相等列不同行對應的索引也是不同行的）

      2、  索引層次：索引高度一般情況下是自平衡的，也就是所有葉節點處於同一層次，且絕大數情況是為2-3層（資料可能是百千萬以上，意味著只要兩至三個IO就可以訪問到相應的索引）。DELETE和UPDATE會影響到TREE的平衡，因為DELETE將把索引標為刪除，但是不會釋放空間，只能等相同值插入時才可以重用。UPDATE事實是上一個DELETE和INSERT的組合。因此如果表有大量的DML操作後，應該重建索引。（Oracle will try to keep every block in the index from about three quarters to completely full although, again, DELETEs and UPDATEs can skew this as well.）

      3、  索引壓縮：索引為分字首和字尾，字首可能重複，字尾為唯一的。可以對相同的前綴進行壓縮以節省空間，消除冗餘資料。雖然壓縮索引可以帶來空間的節省，但是將帶來管理上的複雜，如更改維護等，增加CPU開銷，因此要綜合考慮好了再應用。

    4、  反向鍵索引：RAC環境較適用，由於RAC下可能多個例項更改同一個塊（’Pinging’），導致塊爭用。反向儲存索引列的位元組儲存減少爭用（帶來空間利用率不高）。反向鍵索引由於儲存位置物理上的分離導致多數情況下無法範圍掃描。但是如果有複合反向索引的話，將可以範圍掃描，因為字首列是反向儲存後還是相等的，如果字首列一樣的話，物理儲存位置將是連續的。

      5、  降序索引：8i引入，之前雖然可以用DESC關鍵字來建立索引，但是物理儲存將會忽略。降序索引主要是對索引列按降序進行排序儲存。

      6、  B*tree索引適用範圍：

（1、）透過索引訪問表中小部分資料時。

（2、）當索引能夠提供大多數的查詢需求資訊時，如對索引列進行統計等。

（3、）關於使用索引百分比的一個例子：

      我們經常在各種文件上看到說如果查詢資料的百分比為表總數的20%以下時用索引會有比較好的效果，下面我們透過一個例項說明為什麼這種說法是錯的。

假如有一張表有100,000條記錄，使用者要訪問其中的20,000條，也就是20%，符合我們通常意義上所說的比例。假如這個表每行佔用80個位元組，按一個塊為8KB來算，那麼每個塊差不多儲存100行，整個表佔用1000個資料塊。當我們透過索引訪問表中資料（無序）的時候，由於表中資料是無序的以堆的形式存在的，那麼訪問20,000行將需要20,000個TABLE ACCESS (BY INDEX ROWID)操作，也就是說需要20,000個I/O我們才會訪問到我們需要的資料，這還不包括讀取索引的IO。而整個表一共才1000個資料塊！    

(4 、)CBO與索引的使用：

      USER_INDEXES裡面的CLUSTERING_FACTOR列的值用以表示索引的資料和相應表的資料是否有很好的對應關係。如果該值接近於表的塊數，那麼說明表中的資料分佈是均勻有規則的，這種情況下索引葉塊的索引條目通常指向表中同一個資料塊的行資料；如果接近於表的行數的話，那麼說明表中的資料分佈是很凌亂的，同一葉塊的索引條目指向的行資料可能分散在表中不同的資料塊上，透過索引訪問大範圍資料的話需要重複讀取相同資料塊，導致索引效率不高。

      7、  B*tree索引總結：B*tree索引是ORACLE中最通用的一種索引型別。多數情況下索引對查詢有著積極的影響，但是也應該避免由於使用索引導致的效能下降。索引建立的時機，索引列的選擇這些都是很重要的技巧，必須在開發階段就應該決定好索引的建立。

四、點陣圖索引：

      點陣圖索引從ORACLE7.3開始引入，主要為資料倉儲環境而設計的，不適用於OLTP系統，尤其是有很多併發會話執行DML的系統。點陣圖索引儲存也是按樹形結構儲存的。不同於B*tree索引，點陣圖索引的同一索引條目是儲存有著相同列值的不同行的指標的點陣圖組成。點陣圖索引儲存的值只為1(true),0(false or null)。當我們要透過點陣圖索引對應的行時，ORACLE會先應用內部函式將對應行的位元位進行反轉運算得到行的ROWID，再透過ROWID訪問行資料。點陣圖索引比較適用於低基數列，且基數越低效果越好。還有就是一些諸如查詢參考許多列或者產生COUNT聚集的的查詢也將從點陣圖索引中受益。點陣圖索引在集中讀取的環境中有較高的效率，但是在有很多DML操作的情況下便不適合了。這是因為點陣圖索引產生更改時會鎖定整個索引條目對應的點陣圖，而不是單行的位元位，嚴重影響了併發性。

五、函式索引：

      函式索引是從Oracle 8i開始引入的，具有索引計算列的功能，允許應用程式透過執行自己的函式和運算子有效地擴充套件應用。適用於較少DML的表，對於查詢較多時尤其有效。

    要使用函式索引必須遵從下列前提條件：

（1、）使用CBO

（2、）擁有QUERY REWRITE或GLOBAL QUERY REWRITE許可權

（3、）使用SUBSTR函式只能返回VARCHAR2或RAW資料型別的值

       （4、）QUERY_REWRITE_ENABLE和QUERY_REWRITE_INTERGRITY兩個會話和系統引數必須為TRUE和TRUSTED。前者表示允許最佳化器使用函式索引重寫查詢，後者表示“信任”開發人員的確定性程式碼。

六、索引FAQ：

      1、  索引和NULL：常規的B*tree索引不會儲存全為空的值，聚簇索引和點陣圖索引會儲存全為空的值。

      2、  外來鍵與索引：如果子表外來鍵不存在索引的話，那麼任何對主表主鍵的更新都將會導致鎖定整個子表，額外鎖定了許多行，嚴重抑制了並行性。一般情況下外來鍵最好使用索引，以下情況除外：（1、）沒有從父表中刪除。（2、）不更新父表主鍵的值。（3、）不連線父表和子表。

      3、  為何沒用到索引：

       （1、）謂詞沒有使用索引的主要邊界。如表T在（x,y）上有索引，如果有查詢select * from t where y=5將不會使用索引，因為謂詞中沒有包括列X，而如果改成select x,y from t where y=5就會使用索引。

       （2、）使用如select count(*) from t由於索引列存在全為空值，這樣將不會用到索引。

       （3、）對索引列使用函式：select * from t where function(indexed_column)=value;

       （4、）隱式資料轉換。如索引列是字元列，但列只包括數字資料，使用數字來查詢的話將導致索引失效。應該避免這種情況，總是拿蘋果和蘋果比較，香蕉和香蕉比較。

       （5、）當表中資料發生大範圍更改時，必須及時收集資訊，以使CBO做出正確的判斷，決定索引的使用與否。

       （6、）索引中空間是重用的。只要出現的行重用，索引塊上的空間就可以重用。當一個索引塊全為空時，將返回到freelist中，空間可以重用。

       （7、）誤解：建立索引時，必須將差異數最多的列當做前導列。事實上這是錯誤的，列的放置應該是肯定查詢的需求而定的。