【資料庫使用-oracle索引的建立和分類】一

索引是資料庫中一種可選的資料結構，她通常與表或簇相關。使用者可以在表的一列或數列上建立索引，以提高在此表上執行 SQL 語句的效能。就像本文件的索引可以幫助讀者快速定位所需資訊一樣，Oracle 的索引提供了更為迅速地訪問表資料的方式。正確地使用索引能夠顯著的減少磁碟 I/O。

Oracle 提供了多種型別的索引，可以互為補充地提升查詢效能：

· 平衡樹索引（B-tree index）

· 平衡樹簇索引（B-tree cluster index）

· 雜湊簇索引（hash cluster index）

· 反向鍵索引（reverse key indexes）

· 點陣圖索引（bitmap index）

· 點陣圖連線索引（bitmap join index）

· 函式索引（function-based index）

無論索引是否存在都無需對已有的 SQL 語句進行修改。索引只是提供了一種快速訪問資料的路徑，因此她只會影響查詢的執行速度，但是資料在編輯的過程中，會不斷去建立或者更新索引，會讓整個系統的磁碟開銷非常大，進一步會影響整個系統的執行。因此，當我們要建立索引的時候，一定要問自己，這個索引真的有必要建立麼？

索引在邏輯上和物理上都與其基表（base table）是相互獨立的。使用者可以隨時建立（create）或移除（drop）一個索引，而不會影響其基表或基表上的其他索引。當使用者移除一個索引時，所有的應用程式仍然能夠繼續工作，但是資料訪問速度有可能會降低。作為一種獨立的資料結構，索引需要佔用儲存空間。

當索引被建立後，對其的維護與使用都是 Oracle 自動完成的。當索引所依賴的資料發生插入，更新，刪除等操作時，Oracle 會自動地將這些資料變化反映到相關的索引中，無需使用者的額外操作。

即便索引的基表中插入新的資料，對被索引資料的查詢效能基本上能夠保持穩定不變。但是，如果在一個表上建立了過多的索引，將降低其插入，更新，及刪除的效能。因為 Oracle 必須同時修改與此表相關的索引資訊。

最佳化器可以使用已有的索引來建立（build）新的索引。這將加快新索引的建立速度。

唯一索引和非唯一索引

索引（index）可以是唯一（unique）的或非唯一（nonunique）的。在一個表上建立唯一索引（unique index）能夠保證此表的索引列（一列或多列）不存在重複值。而非唯一索引（nonunique index）並不對索引列值進行這樣的限制。
Oracle 建議使用 CREATE UNIQUE INDEX 語句顯式地建立唯一索引（unique index）。透過主鍵（primary key）或唯一約束（unique constraint）來建立唯一索引不能保證建立新的索引，而且用這些方式建立的索引不能保證為唯一索引。

複合索引

複合索引（composite index）（也被稱為連結索引（concatenated index））是指建立在一個表的多列上的索引。複合索引內的列可以任意排列，她們在資料表中也無需相鄰。

如果一個 SELECT 語句的 WHERE 子句中引用了複合索引（composite index）的全部列（all of the column）或自首列開始且連續的部分列（leading portion of thecolumn），將有助於提高此查詢的效能。因此，索引定義中列的順序是很重要的。大體上說，經常訪問的列（most commonly accessed）或選擇性較大的列（most selective）應該放在前面。

一個常規的（regular）複合索引（composite index）不能超過 32 列，而點陣圖索引（bitmap index）不能超過 30 列。索引中一個鍵值（key value）的總長度大致上不應超過一個資料塊（data block）總可用空間的一半。

索引和鍵

索引（index）與鍵（key）是不同的概念，但是這兩個術語經常被混用。索引是在資料庫中實際儲存的資料結構，使用者可以使用 SQL 語句對其進行建立（create），修改（alter），或移除（drop）。索引提供了一種快速訪問表資料的途徑。而鍵只是一個邏輯概念。鍵的概念主要在 Oracle 的完整性約束（integrity constraint）功能中使用，完整性約束用於保證資料庫中的業務規則（business rule）。

因為 Oracle 也會使用索引（index）來實現某些完整性約束（integrity constraint），因此索引與鍵（key）這兩個術語經常被混用。注意不要將二者混淆。

索引和空值

對於一個資料表的兩行或多行，如果其索引列（key column）中全部非空（non-NULL）的值完全相同（identical），那麼在索引中這些行將被認為是相同的；反之，在索引中這些行將被認為是不同的。因此使用 UNIQUE 索引可以避免將包含 NULL 的行視為相同的。以上討論並不包括索引列的列值（column value）全部為 NULL 的情況。

Oracle 不會將索引列（key column）全部為 NULL 的資料行加入到索引中。不過點陣圖索引（bitmap index）是個例外，簇鍵（cluster key）的列值（column value）全部為 NULL 時也是例外。

函式索引

如果一個函式（function）或表示式（expression）使用了一個表的一列或多列，則使用者可以依據這些函式或表示式為表建立索引，這樣的索引被稱為函式索引（Function-Based Index）。函式索引能夠計算出函式或表示式的值，並將其儲存在索引中。使用者建立的函式索引既可以是平衡樹型別（B-tree index）的，也可以是點陣圖型別（bitmap index）的。

用於建立索引的函式可以是一個數學表示式（arithmetic expression），也可以是使用了 PL/SQL 函式（PL/SQL function），包函式（package function），C 外部呼叫（C callout），或 SQL 函式（SQL function）的表示式。用於建立索引的函式不能包含任何聚合函式（ggregate function），如果為使用者自定義函式，則在宣告中必須使用 DETERMINISTIC關鍵字。如果在一個使用物件型別（object type）的列上建立函式索引，則可以使用此物件的方法（method）作為函式，例如此物件的 map 方法。使用者不能在資料型別為 LOB，REF，或巢狀表（nested table）的列上建立函式索引，也不能在包含 LOB，REF，或巢狀表等資料型別的物件型別列上建立函式索引。

使用函式索引

如果一個 SQL 語句的 WHERE 子句中使用了函式，那麼建立相應的函式索引（function-based index）是提高資料訪問效能的有效機制。表示式（expression）的結果經過計算後將被儲存在索引中。但是當執行 INSERT 和 UPDATE 語句時，Oracle 需要進行函式運算以便維護索引。

例如，如果使用者建立了以下函式索引：

CREATE INDEX idx ON table_1 (a + b * (c – 1),a, b);

當 Oracle 處理如下查詢時就可以使用之前建立的索引：

SELECT a FROM table_1 WHERE a + b * (c – 1)< 100;

使用 UPPER(column_name) 或 LOWER(column_name) 函式建立函式索引（function-based index）有助於與大小寫無關（case-insensitive）的查詢。例如建立以下函式索引：

CREATE INDEX uppercase_idx ON employees (UPPER(first_name));

有助於提高以下查詢的效能：

SELECT * FROM employees WHEREUPPER(first_name) = ‘RICHARD’;

函式索引的最佳化

使用者必須為最佳化器（optimizer）收集關於函式索引（unction-based index）的統計資訊（statistic）。否則處理 SQL 語句時將不會使用此索引。

當一個查詢的 WHERE 子句中含有表示式（expression）時，最佳化器可以對函式索引（function-based index）進行索引區間掃描（index range scan）。例如以下查詢：

SELECT * FROM t WHERE a + b < 10;

如果使用表示式（expression） a+b 建立的索引，最佳化器（optimizer）就能夠進行索引區間掃描（index range scan）。如果謂詞（predicate，即 WHERE 子句）產生的選擇性（selectivity）較低，則對區間掃描極為有利。此外，如果表示式的結果物化在函式索引內（function-based index），最佳化器將能更準確地估計使用此表示式的謂詞的選擇性。

最佳化器（optimizer）能夠將 SQL 語句及函式索引（function-based index）中的表示式解析為表示式樹（expression tree）並進行比較，從而實現表示式匹配。這個比較過程是大小寫無關的（case-insensitive），並將忽略所有空格（blank space）。

逆序鍵索引（reverse）

使用者可以建立逆序鍵索引（reverse key index），此處的逆序指索引列值（index key value）得各個位元組（byte）按倒序排列，而非索引列（index key）逆序排列。在 RAC 環境中，使用這樣的排列方式可以避免由於對索引的修改集中在一小部分葉塊（leaf block）上而造成的效能下降。透過使索引的鍵值逆序排列，可以使插入操作分佈在索引的全部葉塊中。

使用逆序鍵索引（reverse key index）後將無法對此索引進行索引區間掃描（index range scanning），因為在逆序鍵索引中，詞彙上（lexically）相鄰的索引鍵（index key）在儲存上未必相鄰。因此在逆序鍵索引上只能進行確定鍵掃描（fetch-by-key scan）或全索引掃描（full-index scan）。

有些情況下，使用逆序鍵索引（reverse key index）可以令 RAC 環境下的 OLTP 應用效率更高。例如，為一個 e-mail 應用中的所有郵件進行索引：由於使用者可能儲存舊的郵件，因此索引必須做到既能快速訪問最新郵件，也能快速訪問舊郵件。

使用者使用 REVERSE 就可以輕易地建立逆序鍵索引（reverse key index）。在 CREATE INDEX語句中使用 REVERSE 關鍵字作為建立索引的選項：

CREATE INDEX i ON t (a,b,c) REVERSE;

使用者也可以在 REBUILD 子句後新增 NOREVERSE 關鍵字將一個逆序鍵索引（reverse key index）轉換為常規的索引：

ALTER INDEX i REBUILD NOREVERSE;

如果 REBUILD 子句後沒有使用 NOREVERSE 關鍵字，那麼逆序鍵索引（reverse key index）被重建後仍將保持逆序。

點陣圖索引

點陣圖索引也只一種非常重要的索引，在一些特定的環境中，有非常好的效果，但是非常容易被濫用，使用前後需要仔細驗證看是否達到預期效果。

表分析

有的時候，我們會發現雖然有索引，但是執行計劃不走索引，全表掃描跑的嘩嘩的，這個時候要檢視下這個表的最後的分析時間，如果DBA沒有及時做表分析，那麼索引可能用不到。

select * from user_tables t ;

找到對應的表的last_analyzed時間；如果時間太老或者沒有時間，那麼說明這張表需要做表分析了。

表分析一般要在業務效能不太繁忙的時候做，先了解一下什麼是表分析：

簡單的說，就是收集表和索引的資訊，CBO根據這些資訊決定SQL最佳的執行路徑。透過對錶的分析，可以產生一些統計資訊，透過這些資訊oracle的最佳化程式可以最佳化。

oracle的聯機文件描述了analyze的做用：

Use the ANALYZE statement to collect non-optimizer statistics, forexample, to:

--使用analyze功能可以收集一些沒有進行最佳化的統計資訊，例如：

Collect or delete statistics about an index or index partition, table ortable partition,

--聚集或刪除索引或索引分割槽，表或表分割槽

index-organized table, cluster, or scalar object attribute.

--索引表，串，或遊標的目標屬性等統計資訊

Validate the structure of an index or index partition, table or tablepartition, index-organized table,

--增加下列結構的有效性：索引或索引分割槽，表或表分割槽，索引表

cluster, or object reference (REF).

--串，目標參照

Identify migrated and chained rows of a table or cluster.

--鑑別一個表或串中已經被移動的抑或被鎖住的列。

表分析的語句參考：

analyze table tableName compute statistics;

平衡樹結構的優勢

平衡樹資料結構（B-tree structure）具有以下優勢：

· 平衡樹（B-tree）內所有葉塊（leaf block）的深度相同，因此獲取索引內任何位置的資料所需的時間大致相同。

· 平衡樹索引（B-tree index）能夠自動保持平。

· 平衡樹內的所有塊容量平均在總容量的 3/4 左右。

· 在大區間（wide range）範圍內進行查詢時，無論匹配個別值（exact match）還是搜尋一個區間（range search），平衡樹都能提供較好的查詢效能。

· 資料插入（insert），更新（update），及刪除（delete）的效率較高，且易於維護鍵值的順序（key order）

· 大型表，小型表利用平衡樹進行搜尋的效率都較好，且搜尋效率不會因資料增長而降低。

索引唯一掃描

索引唯一掃描（index unique scan）是效率最高的資料訪問方式之一。從平衡樹索引（B-tree index）中獲取資料時將採用此種方式。當一個唯一索引（採用平衡樹結構）的全部列都包含在查詢條件中，且查詢體條件表示式均為等號（equality）時，最佳化器將選擇使用索引唯一掃描。

索引區間掃描

當訪問選擇性較大的資料（selective data）時 Oracle 常進行索引區間掃描（index range scan）。掃描區間可以是封閉的（bounded）（兩端均封閉），也可以是不封閉的（unbounded）（一端或兩端均不封閉）。掃描所返回的資料按照索引列的升序進行排列，對於索引值相同的行將按 ROWID 的升序排列。

效能上及儲存上的考慮

鍵壓縮（key compression）能夠節約大量儲存空間，因此使用者可以在一個索引塊（index block）記憶體儲更多的索引鍵（index key），從而減少 I/O，提高效能。

鍵壓縮（key compression）能夠減少索引所需的儲存空間，但索引掃描時需要重構（reconstruct）鍵值（key value），因此增加了 CPU 的負擔。此外來鍵壓縮也會帶來一些儲存開銷，每個字首（prefix entry）需要 4 位元組（byte）的管理開銷。

使用鍵壓縮

鍵壓縮（key compression）在多種情況下都能夠發揮作用，例如：

對於非唯一索引（nonunique index），Oracle 會在每個重複的索引鍵（index key）之後新增 rowid 以便區分。如果使用了鍵壓縮，在一個索引塊（index block）內，Oracle 只需將重複的索引鍵作為字首（（prefix entry））儲存一次，並用各行的 rowid 作為字尾（suffix entry）。

唯一索引（nonunique index）中也存在相同的情況。例如唯一索引（stock_ticker，transaction_time）的含義是（專案，時間戳），通常數千條記錄中stock_ticker 的值是相同的，但她們對應的 transaction_time 值各不相同。使用了鍵壓縮後，一個索引塊中每個 stock_ticker 值作為字首只需儲存一次，而各個transaction_time 值則作為字尾儲存，並引用一個共享的 stock_ticker 字首。

在一個包含 VARRAY 或 NESTED TABLE 資料型別（datatype）的索引表（index-organized table）中，這些collection 型別中各個元素（element）的物件識別符號（object identifier）是重複的。使用者可以使用鍵壓縮以避免重複儲存這些物件識別符號。

有些情況無法使用鍵壓縮（key compression）。例如，一個只有一個索引鍵（index key）的唯一索引（unique index）就無法使用鍵壓縮，因為索引鍵中不存在可供共享的分組片段（grouping piece）。