Oracle中索引的創建和使用（總結分享）

本篇文章給大家帶來了Oracle中索引的創建和使用的相關知識，希望對大家有幫助。

OLTP系統索引創建

創建索引的作用

1、通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。

2、可以大大加快數據的檢索速度，這也是創建索引的最主要的原因。

3、可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。

4、使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。

5、通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能

如何選擇索引列

一、應該建索引列的特點

1）在經常需要搜索的列上，可以加快搜索的速度；

2）在作為主鍵的列上，強制該列的唯一性和組織表中數據的排列結構；

3）在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；

4）在經常需要根據范圍進行搜索的列上創建索引，因為索引已經排序，其指定的范圍 是連續的；

5）在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；

6）在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。

二、不應該建索引列的特點

1）對于那些在查詢中很少使用或者參考的列不應該創建索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，并不能提高查詢速度。相反，由于增加了索引，反而 降低了系統的維護速度和增大了空間需求。

2）對于那些只有很少數據值的列也不應該增加索引。這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的數據行占了表中數據行的很大比例，即需要在表中搜索的數據行的比例很大。增加索引，并不能明顯加快檢索速度。

3）對于那些定義為blob數據類型的列不應該增加索引。這是因為，這些列的數據量要么相當大，要么取值很少。

4）當修改性能遠遠大于檢索性能時，不應該創建索引。這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大于檢索性能時，不應該創建索引。（數據量龐大，考慮創建分區索引）

索引的創建語法

CREATEUNIUQE | BITMAP INDEX <schema>.<index_name> ON <schema>.<table_name> (<column_name> | <expression> ASC | DESC, <column_name> | <expression> ASC | DESC,...) TABLESPACE <tablespace_name> STORAGE <storage_settings> LOGGING | NOLOGGING COMPUTE STATISTICS NOCOMPRESS | COMPRESS<nn> NOSORT | REVERSE PARTITION | GLOBAL PARTITION<partition_setting>

相關說明

1） UNIQUE | BITMAP：指定 UNIQUE為唯一值索引， BITMAP為位圖索引，省略為 B-Tree索引。

2） <column_name> |<expression> ASC | DESC：可以對多列進行聯合索引，當為expression 時即―基于函數的索引

3） TABLESPACE：指定存放索引的表空間(索引和原表不在一個表空間時效率更高)

4） STORAGE：可進一步設置表空間的存儲參數

5） LOGGING | NOLOGGING：是否對索引產生重做日志(對大表盡量使用 NOLOGGING來減少占用空間并提高效率)

6） COMPUTESTATISTICS：創建新索引時收集統計信息

7） NOCOMPRESS | COMPRESS<nn>：是否使用―鍵壓縮‖(使用鍵壓縮可以刪除一個鍵列中出現的重復值)

8） NOSORT | REVERSE： NOSORT 表示與表中相同的順序創建索引， REVERSE表示相反順序存儲索引值

9） PARTITION | NOPARTITION：可以在分區表和未分區表上對創建的索引進行分區

索引使用誤區

限制索引

限制索引是一些沒有經驗的開發人員經常犯的錯誤之一。在 SQL中有很多陷阱會使一

些索引無法使用。下面討論一些常見的問題：

1、使用不等于操作符（ <>、 !=）

下面的查詢即使在cust_rating列有一個索引，查詢語句仍然執行一次全表掃描。

    select cust_Id,cust_name from customers wherecust_rating<> 'aa';

把上面的語句改成如下的查詢語句，這樣，在采用基于規則的優化器而不是基于代價的優化器（更智能）時，將會使用索引。

   select cust_Id,cust_name fromcustomers where cust_rating<'aa' orcust_rating > 'aa';

特別注意：通過把不等于操作符改成OR條件，就可以使用索引，以避免全表掃描。

2、 使用 IS NULL或 IS NOT NULL

使用ISNULL或ISNOT NULL同樣會限制索引的使用。因為NULL值并沒有被定義。

在 SQL語句中使用NULL會有很多的麻煩。因此建議開發人員在建表時，把需要索引的列設成NOT NULL。如果被索引的列在某些行中存在NULL值，就不會使用這個索引（除非索引是一個位圖索引，關于位圖索引在稍后在詳細討論）。

3、使用函數

如果不使用基于函數的索引，那么在 SQL語句的 WHERE子句中對存在索引的列使用函數時，會使優化器忽略掉這些索引。 下面的查詢不會使用索引（只要它不是基于函數的索引）

select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81';

把上面的語句改成下面的語句，這樣就可以通過索引進行查找。

select empno,ename,deptno from emp where hiredate<(to_date('01-MAY-81')+0.9999);

4、比較不匹配的數據類型

也是比較難于發現的性能問題之一。 注意下面查詢的例子，account_number是一個VARCHAR2類型,在 account_number字段上有索引。

下面的語句將執行全表掃描：

select bank_name,address,city,state,zip from banks whereaccount_number = 990354;

Oracle 可以自動把 where子句變成to_number(account_number)=990354，這樣就限

制了索引的使用,改成下面的查詢就可以使用索引：

select bank_name,address,city,state,zip from banks where account_number='990354';

特別注意： 不匹配的數據類型之間比較會讓Oracle自動限制索引的使用,即便對這個查詢執行ExplainPlan也不能讓您明白為什么做了一次―全表掃描。

5、查詢索引

查 詢 DBA_INDEXES視 圖 可 得 到 表 中 所 有 索 引 的 列表 ， 注 意 只 能 通 過USER_INDEXES的方法來檢索模式(schema)的索引。訪問 USER_IND_COLUMNS視圖可得到一個給定表中被索引的特定列。

6、 組合索引

當某個索引包含有多個已索引的列時，稱這個索引為組合（concatented）索引。在Oracle9i引入跳躍式掃描的索引訪問方法之前，查詢只能在有限條件下使用該索引。比如：表 emp 有一個組合索引鍵，該索引包含了 empno、 ename和 deptno。在Oracle9i之前除非在 where之句中對第一列（empno）指定一個值，否則就不能使用這個索引鍵進行一次范圍掃描。

特別注意：在Oracle9i之前，只有在使用到索引的前導索引時才可以使用組合索引

索引分類

Oracle提供了大量索引選項。知道在給定條件下使用哪個選項對于一個應用程序的性能來說非常重要。一個錯誤的選擇可能會引發死鎖，并導致數據庫性能急劇下降或進程終止。而如果做出正確的選擇，則可以合理使用資源，使那些已經運行了幾個小時甚至幾天的進程在幾分鐘得以完成，下面就將簡單的討論每個索引選項。

在這里討論如下的索引類型：

• B樹索引(默認類型)

• 位圖索引

• HASH索引

• 索引組織表索引

• 反轉鍵(reverse key)索引

• 基于函數的索引

• 分區索引(本地和全局索引)

• 位圖連接索引

B樹索引 (默認類型)

B樹索引在Oracle中是一個通用索引。在創建索引時它就是默認的索引類型。B樹索引可以是一個列的(簡單)索引，也可以是組合/復合(多個列)的索引。B樹索引最多可以包括32列。

在下圖的例子中，B樹索引位于雇員表的last_name列上。這個索引的二元高度為3；接下來，Oracle會穿過兩個樹枝塊(branch block)，到達包含有ROWID的樹葉塊。在每個樹枝塊中，樹枝行包含鏈中下一個塊的ID號。

樹葉塊包含了索引值、ROWID，以及指向前一個和后一個樹葉塊的指針。Oracle可以從兩個方向遍歷這個二叉樹。B樹索引保存了在索引列上有值的每個數據行的ROWID值。Oracle不會對索引列上包含NULL值的行進行索引。如果索引是多個列的組合索引，而其中列上包含NULL值，這一行就會處于包含NULL值的索引列中，且將被處理為空(視為NULL)

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

B-tree特點：

• 適合與大量的增、刪、改（OLTP）

• 不能用包含OR操作符的查詢；

• 適合高基數的列（唯一值多）

• 典型的樹狀結構；

• 每個結點都是數據塊；

• 大多都是物理上一層、兩層或三層不定，邏輯上三層；

• 葉子塊數據是排序的，從左向右遞增；

• 在分支塊和根塊中放的是索引的范圍；

位圖索引

位圖索引非常適合于決策支持系統(Decision Support System，DSS)和數據倉庫，它們不應該用于通過事務處理應用程序訪問的表。它們可以使用較少到中等基數(不同值的數量)的列訪問非常大的表。

盡管位圖索引最多可達30個列，但通常它們都只用于少量的列。

例如，您的表可能包含一個稱為Sex的列，它有兩個可能值：男和女。這個基數只為2，如果用戶頻繁地根據Sex列的值查詢該表，這就是位圖索引的基列。當一個表內包含了多個位圖索引時，您可以體會到位圖索引的真正威力。如果有多個可用的位圖索引，Oracle就可以合并從每個位圖索引得到的結果集，快速刪除不必要的數據。

Bitmapt特點：

適合與決策支持系統；做 UPDATE代價非常高

非常適合 OR操作符的查詢；基數比較少的時候才能建位圖索引；

技巧：對于有較低基數的列需要使用位圖索引。性別列就是這樣一個例子，它有兩個可能值：男或女(基數僅為2)。位圖對于低基數(少量的不同值)列來說非常快，這是因為索引的尺寸相對于B樹索引來說小了很多。因為這些索引是低基數的 B 樹索引，所以非常小，因此您可以經常檢索表中超過半數的行，并且仍使用位圖索引。

當大多數條目不會向位圖添加新的值時，位圖索引在批處理(單用戶)操作中加載表(插入操作)方面通常要比B樹做得好。當多個會話同時向表中插入行時不應該使用位圖索引，在大多數事務處理應用程序中都會發生這種情況。

示例

下面來看一個示例表PARTICIPANT，該表包含了來自個人的調查數據。列Age_Code、Income_Level、Education_Level和Marital_Status都包括了各自的位圖索引。 下圖顯示了每個直方圖中的數據平衡情況，以及對訪問每個位圖索引的查詢的執行路徑。圖中的執行路徑顯示了有多少個位圖索引被合并，可以看出性能得到了顯著的提高。

如上圖圖所示，優化器依次使用4個單獨的位圖索引，這些索引的列在WHERE子句中被引用。每個位圖記錄指針(例如0或1)，用于示表中的哪些行包含位圖中的已知值。有了這些信息后，Oracle就執行BITMAP AND操作以查找將從所有4個位圖中返回哪些行。該值然后被轉換為ROWID值，并且查詢繼續完成剩余的處理工作。注意，所有4個列都有非常低的基數，使用索引可以非常快速地返回匹配的行。

技巧：在一個查詢中合并多個位圖索引后，可以使性能顯著提高。位圖索引使用固定長度的數據類型要比可變長度的數據類型好。較大尺寸的塊也會提高對位圖索引的存儲和讀取性能。

下面的查詢可顯示索引類型。

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環境中使用位圖索引

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索引在有限制條件(需要指定一個確定的值而不是一個值范圍)的情況下非常有用

索引組織表

索引組織表會把表的存儲結構改成B樹結構，以表的主鍵進行排序。這種特殊的表和其他類型的表一樣，可以在表上執行所有的DML和DDL語句。由于表的特殊結構， ROWID并沒有被關聯到表的行上。

對于一些涉及精確匹配和范圍搜索的語句，索引組織表提供了一種基于鍵的快速數據訪問機制。基于主鍵值的UPDATE和DELETE語句的性能也同樣得以提高， 這是因為行在物理上有序。

由于鍵列的值在表和索引中都沒有重復，存儲所需要的空間也隨之減少。如果不會頻繁地根據主鍵列查詢數據，則需要在索引組織表中的其他列上創建二級索引。不會頻繁根據主鍵查詢表的應用程序不會了解到使用索引組織表的全部優點。 對于總是通過對主鍵的精確匹配或范圍掃描進行訪問的表，就需要考慮使用索引組織表。

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

反轉鍵索引

當載入一些有序數據時，索引肯定會碰到與I/O相關的一些瓶頸。在數據載入期間，某部分索引和磁盤肯定會比其他部分使用頻繁得多。為了解決這個問題，可以把索引表空間存放在能夠把文件物理分割在多個磁盤上的磁盤體系結構上。

為了解決這個問題，Oracle還提供了一種反轉鍵索引的方法。如果數據以反轉鍵索引存儲，這些數據的值就會與原先存儲的數值相反。這樣，數據1234、1235和1236就被存儲成4321、5321和6321。結果就是索引會為每次新插入的行更新不同的索引塊。

技巧：如果您的磁盤容量有限，同時還要執行大量的有序載入，就可以使用反轉鍵索引。

不可以將反轉鍵索引與位圖索引或索引組織表結合使用。 因為不能對位圖索引和索引組織表進行反轉鍵處理。

基于數的索引

可以在表中創建基于函數的索引。如果沒有基于函數的索引，任何在列上執行了函數的查詢都不能使用這個列的索引。例如，下面的查詢就不能使用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!!!

分區索引

分區索引就是簡單地把一個索引分成多個片斷。通過把一個索引分成多個片斷，可以訪問更小的片斷(也更快)，并且可以把這些片斷分別存放在不同的磁盤驅動器上(避免I/O問題)。

B樹和位圖索引都可以被分區，而HASH索引不可以被分區。可以有好幾種分區方法：表被分區而索引未被分區；表未被分區而索引被分區；表和索引都被分區。

不管采用哪種方法，都必須使用基于成本的優化器。分區能夠提供