1.Oracle提供了大量索引選項。知道在給定條件下使用哪個選項對於一個應用程式的效能來說非常重要。一個錯誤的選擇可能會引發死鎖，並導致資料庫效能急劇下降或程式終止。而如果做出正確的選擇，則可以合理使用資源，使那些已經執行了幾個小時甚至幾天的程式在幾分鐘得以完成，這樣會使您立刻成為一位英雄。下面就將簡單的討論每個索引選項。

下面討論的索引型別：
B樹索引(預設型別)
點陣圖索引
HASH索引
索引組織表索引
反轉鍵(reverse key)索引
基於函式的索引
分割槽索引(本地和全域性索引)
點陣圖連線索引

2.1 B樹索引 (預設型別)
B樹索引在Oracle中是一個通用索引。在建立索引時它就是預設的索引型別。B樹索引可以是一個列的(簡單)索引，也可以是組合/複合(多個列)的索引。B樹索引最多可以包括32列。

技巧：索引列的值都儲存在索引中。因此，可以建立一個組合(複合)索引，這些索引可以直接滿足查詢，而不用訪問表。這就不用從表中檢索資料，從而減少了I/O量。

B-tree 特點：
適合與大量的增、刪、改（OLTP）
不能用包含OR運算子的查詢；
適合高基數的列（唯一值多）
典型的樹狀結構；
每個結點都是資料塊；
大多都是物理上一層、兩層或三層不定，邏輯上三層；
葉子塊資料是排序的，從左向右遞增；
在分支塊和根塊中放的是索引的範圍；

2.2 點陣圖索引
點陣圖索引非常適合於決策支援系統(Decision Support System，DSS)和資料倉儲，它們不應該用於透過事務處理應用程式訪問的表。它們可以使用較少到中等基數(不同值的數量)的列訪問非常大的表。儘管點陣圖索引最多可達30個列，但通常它們都只用於少量的列。
例如，您的表可能包含一個稱為Sex的列，它有兩個可能值：男和女。這個基數只為2，如果使用者頻繁地根據Sex列的值查詢該表，這就是點陣圖索引的基列。當一個表內包含了多個點陣圖索引時，您可以體會到點陣圖索引的真正威力。如果有多個可用的點陣圖索引，Oracle就可以合併從每個點陣圖索引得到的結果集，快速刪除不必要的資料。

Bitmapt 特點：
適合與決策支援系統；
做UPDATE代價非常高；
非常適合OR運算子的查詢；
基數比較少的時候才能建點陣圖索引；

技巧：對於有較低基數的列需要使用點陣圖索引。性別列就是這樣一個例子，它有兩個可能值：男或女(基數僅為2)。點陣圖對於低基數(少量的不同值)列來說非常快，這是因為索引的尺寸相對於B樹索引來說小了很多。因為這些索引是低基數的B樹索引，所以非常小，因此您可以經常檢索表中超過半數的行，並且仍使用點陣圖索引。
當大多數條目不會向點陣圖新增新的值時，點陣圖索引在批處理(單使用者)操作中載入表(插入操作)方面通常要比B樹做得好。當多個會話同時向表中插入行時不應該使用點陣圖索引，在大多數事務處理應用程式中都會發生這種情況。

技巧：在一個查詢中合併多個點陣圖索引後，可以使效能顯著提高。點陣圖索引使用固定長度的資料型別要比可變長度的資料型別好。較大尺寸的塊也會提高對點陣圖索引的儲存和讀取效能。

下面的查詢可顯示索引型別。
SQL> select index_name, index_type from user_indexes;
INDEX_NAME INDEX_TYPE
------------------------------ ----------------------
TT_INDEX NORMAL
IX_CUSTADDR_TP NORMAL
B樹索引作為NORMAL列出；而點陣圖索引的型別值為BITMAP。

技巧：如果要查詢點陣圖索引列表，可以在USER _INDEXES檢視中查詢index_type列。
建議不要在一些聯機事務處理(OLTP)應用程式中使用點陣圖索引。B樹索引的索引值中包含ROWID，這樣Oracle就可以在行級別上鎖定索引。點陣圖索引儲存為壓縮的索引值，其中包含了一定範圍的ROWID，因此Oracle必須針對一個給定值鎖定所有範圍內的ROWID。這種鎖定型別可能在某些DML語句中造成死鎖。SELECT語句不會受到這種鎖定問題的影響。
點陣圖索引的使用限制：
基於規則的最佳化器不會考慮點陣圖索引。
當執行ALTER TABLE語句並修改包含有點陣圖索引的列時，會使點陣圖索引失效。
點陣圖索引不包含任何列資料，並且不能用於任何型別的完整性檢查。
點陣圖索引不能被宣告為唯一索引。
點陣圖索引的最大長度為30。

技巧：不要在繁重的OLTP環境中使用點陣圖索引

2.3 HASH索引
使用HASH索引必須要使用HASH叢集。建立一個叢集或HASH叢集的同時，也就定義了一個叢集鍵。這個鍵告訴Oracle如何在叢集上儲存表。在儲存資料時，所有與這個叢集鍵相關的行都被儲存在一個資料庫塊上。如果資料都儲存在同一個資料庫塊上，並且將HASH索引作為WHERE子句中的確切匹配，Oracle就可以透過執行一個HASH函式和I/O來訪問資料——而透過使用一個二元高度為4的B樹索引來訪問資料，則需要在檢索資料時使用4個I/O。如下圖所示，其中的查詢是一個等價查詢，用於匹配HASH列和確切的值。Oracle可以快速使用該值，基於HASH函式確定行的物理儲存位置。
HASH索引可能是訪問資料庫中資料的最快方法，但它也有自身的缺點。叢集鍵上不同值的數目必須在建立HASH叢集之前就要知道。需要在建立HASH叢集的時候指定這個值。低估了叢集鍵的不同值的數字可能會造成叢集的衝突(兩個叢集的鍵值擁有相同的HASH值)。這種衝突是非常消耗資源的。衝突會造成用來儲存額外行的緩衝溢位，然後造成額外的I/O。如果不同HASH值的數目已經被低估，您就必須在重建這個叢集之後改變這個值。
ALTER CLUSTER命令不能改變HASH鍵的數目。HASH叢集還可能浪費空間。如果無法確定需要多少空間來維護某個叢集鍵上的所有行，就可能造成空間的浪費。如果不能為叢集的未來增長分配好附加的空間，HASH叢集可能就不是最好的選擇。如果應用程式經常在叢集表上進行全表掃描，HASH叢集可能也不是最好的選擇。由於需要為未來的增長分配好叢集的剩餘空間量，全表掃描可能非常消耗資源。
在實現HASH叢集之前一定要小心。您需要全面地觀察應用程式，保證在實現這個選項之前已經瞭解關於表和資料的大量資訊。通常，HASH對於一些包含有序值的靜態資料非常有效。

技巧：HASH索引在有限制條件(需要指定一個確定的值而不是一個值範圍)的情況下非常有用。

2.4 索引組織表
索引組織表會把表的儲存結構改成B樹結構，以表的主鍵進行排序。這種特殊的表和其他型別的表一樣，可以在表上執行所有的DML和DDL語句。由於表的特殊結構，ROWID並沒有被關聯到表的行上。
對於一些涉及精確匹配和範圍搜尋的語句，索引組織表提供了一種基於鍵的快速資料訪問機制。基於主鍵值的UPDATE和DELETE語句的效能也同樣得以提高，這是因為行在物理上有序。由於鍵列的值在表和索引中都沒有重複，儲存所需要的空間也隨之減少。
如果不會頻繁地根據主鍵列查詢資料，則需要在索引組織表中的其他列上建立二級索引。不會頻繁根據主鍵查詢表的應用程式不會了解到使用索引組織表的全部優點。對於總是透過對主鍵的精確匹配或範圍掃描進行訪問的表，就需要考慮使用索引組織表。

技巧：可以在索引組織表上建立二級索引。

2.5 反轉鍵索引
當載入一些有序資料時，索引肯定會碰到與I/O相關的一些瓶頸。在資料載入期間，某部分索引和磁碟肯定會比其他部分使用頻繁得多。為了解決這個問題，可以把索引表空間存放在能夠把檔案物理分割在多個磁碟上的磁碟體系結構上。
為了解決這個問題，Oracle還提供了一種反轉鍵索引的方法。如果資料以反轉鍵索引儲存，這些資料的值就會與原先儲存的數值相反。這樣，資料1234、1235和1236就被儲存成4321、5321和6321。結果就是索引會為每次新插入的行更新不同的索引塊。

技巧：如果您的磁碟容量有限，同時還要執行大量的有序載入，就可以使用反轉鍵索引。
不可以將反轉鍵索引與點陣圖索引或索引組織表結合使用。因為不能對點陣圖索引和索引組織表進行反轉鍵處理。

2.6 基於函式的索引
可以在表中建立基於函式的索引。如果沒有基於函式的索引，任何在列上執行了函式的查詢都不能使用這個列的索引。例如，下面的查詢就不能使用JOB列上的索引，除非它是基於函式的索引：
select * from emp where UPPER(job) = 'MGR';
下面的查詢使用JOB列上的索引，但是它將不會返回JOB列具有Mgr或mgr值的行：
select * from emp where job = 'MGR';

可以建立這樣的索引，允許索引訪問支援基於函式的列或資料。可以對列表示式UPPER(job)建立索引，而不是直接在JOB列上建立索引，如：
create index EMP$UPPER_JOB on emp(UPPER(job));

儘管基於函式的索引非常有用，但在建立它們之前必須先考慮下面一些問題：
能限制在這個列上使用的函式嗎？如果能，能限制所有在這個列上執行的所有函式嗎
是否有足夠應付額外索引的儲存空間？
在每列上增加的索引數量會對針對該表執行的DML語句的效能帶來何種影響？
基於函式的索引非常有用，但在實現時必須小心。在表上建立的索引越多，INSERT、UPDATE和DELETE語句的執行就會花費越多的時間。

注意：對於最佳化器所使用的基於函式的索引來說，必須把初始引數QUERY _REWRITE _ ENABLED設定為TRUE。

示例：
select count(*) from sample where ratio(balance,limit) >.5;
Elapsed time: 20.1 minutes

create index ratio_idx1 on sample (ratio(balance, limit));

select count(*) from sample where ratio(balance,limit) >.5;
Elapsed time: 7 seconds!!!

2.7 分割槽索引
分割槽索引就是簡單地把一個索引分成多個片斷。透過把一個索引分成多個片斷，可以訪問更小的片斷(也更快)，並且可以把這些片斷分別存放在不同的磁碟驅動器上(避免I/O問題)。B樹和點陣圖索引都可以被分割槽，而HASH索引不可以被分割槽。可以有好幾種分割槽方法：表被分割槽而索引未被分割槽；表未被分割槽而索引被分割槽；表和索引都被分割槽。不管採用哪種方法，都必須使用基於成本的最佳化器。分割槽能夠提供更多可以提高效能和可維護性的可能性
有兩種型別的分割槽索引：本地分割槽索引和全域性分割槽索引。每個型別都有兩個子型別，有字首索引和無字首索引。表各列上的索引可以有各種型別索引的組合。如果使用了點陣圖索引，就必須是本地索引。把索引分割槽最主要的原因是可以減少所需讀取的索引的大小，另外把分割槽放在不同的表空間中可以提高分割槽的可用性和可靠性。
在使用分割槽後的表和索引時，Oracle還支援並行查詢和並行DML。這樣就可以同時執行多個程式，從而加快處理這條語句。
2.7.1.本地分割槽索引(通常使用的索引)
可以使用與表相同的分割槽鍵和範圍界限來對本地索引分割槽。每個本地索引的分割槽只包含了它所關聯的表分割槽的鍵和ROWID。本地索引可以是B樹或點陣圖索引。如果是B樹索引，它可以是唯一或不唯一的索引。
這種型別的索引支援分割槽獨立性，這就意味著對於單獨的分割槽，可以進行增加、擷取、刪除、分割、離線等處理，而不用同時刪除或重建索引。Oracle自動維護這些本地索引。本地索引分割槽還可以被單獨重建，而其他分割槽不會受到影響。

2.7.1.1 有字首的索引
有字首的索引包含了來自分割槽鍵的鍵，並把它們作為索引的前導。例如，讓我們再次回顧participant表。在建立該表後，使用survey_id和survey_date這兩個列進行範圍分割槽，然後在survey_id列上建立一個有字首的本地索引，如下圖所示。這個索引的所有分割槽都被等價劃分，就是說索引的分割槽都使用表的相同範圍界限來建立。

技巧：本地的有字首索引可以讓Oracle快速剔除一些不必要的分割槽。也就是說沒有包含WHERE條件子句中任何值的分割槽將不會被訪問，這樣也提高了語句的效能。

2.7.1.2 無字首的索引
無字首的索引並沒有把分割槽鍵的前導列作為索引的前導列。若使用有同樣分割槽鍵(survey_id和survey_date)的相同分割槽表，建立在survey_date列上的索引就是一個本地的無字首索引，如下圖所示。可以在表的任一列上建立本地無字首索引，但索引的每個分割槽只包含表的相應分割槽的鍵值。

如果要把無字首的索引設為唯一索引，這個索引就必須包含分割槽鍵的子集。在這個例子中，我們必須把包含survey和(或)survey_id的列進行組合(只要survey_id不是索引的第一列，它就是一個有字首的索引)。

技巧：對於一個唯一的無字首索引，它必須包含分割槽鍵的子集。

2.7.2. 全域性分割槽索引
全域性分割槽索引在一個索引分割槽中包含來自多個表分割槽的鍵。一個全域性分割槽索引的分割槽鍵是分割槽表中不同的或指定一個範圍的值。在建立全域性分割槽索引時，必須定義分割槽鍵的範圍和值。全域性索引只能是B樹索引。Oracle在預設情況下不會維護全域性分割槽索引。如果一個分割槽被擷取、增加、分割、刪除等，就必須重建全域性分割槽索引，除非在修改表時指定ALTER TABLE命令的UPDATE GLOBAL INDEXES子句。

2.7.2.1 有字首的索引
通常，全域性有字首索引在底層表中沒有經過對等分割槽。沒有什麼因素能限制索引的對等分割槽，但Oracle在生成查詢計劃或執行分割槽維護操作時，並不會充分利用對等分割槽。如果索引被對等分割槽，就必須把它建立為一個本地索引，這樣Oracle可以維護這個索引，並使用它來刪除不必要的分割槽，如下圖所示。在該圖的3個索引分割槽中，每個分割槽都包含指向多個表分割槽中行的索引條目。

分割槽的、全域性有字首索引

技巧：如果一個全域性索引將被對等分割槽，就必須把它建立為一個本地索引，這樣Oracle可以維護這個索引，並使用它來刪除不必要的分割槽。

2.7.2.2 無字首的索引
Oracle不支援無字首的全域性索引。

2.8 點陣圖連線索引
點陣圖連線索引是基於兩個表的連線的點陣圖索引，在資料倉儲環境中使用這種索引改進連線維度表和事實表的查詢的效能。建立點陣圖連線索引時，標準方法是連線索引中常用的維度表和事實表。當使用者在一次查詢中結合查詢事實表和維度表時，就不需要執行連線，因為在點陣圖連線索引中已經有可用的連線結果。透過壓縮點陣圖連線索引中的ROWID進一步改進效能，並且減少訪問資料所需的I/O數量。

建立點陣圖連線索引時，指定涉及的兩個表。相應的語法應該遵循如下模式：
create bitmap index FACT_DIM_COL_IDX on FACT(DIM.Descr_Col) from FACT, DIM
where FACT.JoinCol = DIM.JoinCol;

點陣圖連線的語法比較特別，其中包含FROM子句和WHERE子句，並且引用兩個單獨的表。索引列通常是維度表中的描述列——就是說，如果維度是CUSTOMER，並且它的主鍵是CUSTOMER_ID，則通常索引Customer_Name這樣的列。如果事實表名為SALES，可以使用如下的命令建立索引：
create bitmap index SALES_CUST_NAME_IDX
on SALES(CUSTOMER.Customer_Name) from SALES, CUSTOMER
where SALES.Customer_ID=CUSTOMER.Customer_ID;

如果使用者接下來使用指定Customer_Name列值的WHERE子句查詢SALES和CUSTOMER表，最佳化器就可以使用點陣圖連線索引快速返回匹配連線條件和Customer_Name條件的行。

點陣圖連線索引的使用一般會受到限制：
1）只可以索引維度表中的列。
2）用於連線的列必須是維度表中的主鍵或唯一約束；如果是複合主鍵，則必須使用連線中的每一列。
3）不可以對索引組織表建立點陣圖連線索引，並且適用於常規點陣圖索引的限制也適用於點陣圖連線索引

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

相關文章