ORACLE用索引提高效率

索引是表的一個概念部分,用來提高檢索資料的效率. 實際上,oracle使用了一個複雜的自平衡b-tree結構. 通常,通過索引查詢資料比全表掃描要快. 當oracle找出執行查詢和update語句的最佳路徑時, oracle優化器将使用索引. 同樣在聯結多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.

除了那些long或long raw資料類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特别有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率.

雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來

存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味着每條記錄的insert , delete , update将為此多付出4 , 5 次的磁盤i/o . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢.

譯者按:

定期的重構索引是有必要的.

alter index <indexname> rebuild <tablespacename>

26. 索引的操作

oracle對索引有兩種通路模式.

索引唯一掃描 ( index unique scan)

大多數情況下, 優化器通過where子句通路index.

例如:

表lodging有兩個索引 : 建立在lodging列上的唯一性索引lodging_pk和建立在manager列上的非唯一性索引lodging$manager.

select *

from lodging

where lodging = ‘rose hill';

在内部 , 上述sql将被分成兩步執行, 首先 , lodging_pk 索引将通過索引唯一掃描的方式被通路 , 獲得相對應的rowid, 通過rowid通路表的方式 執行下一步檢索.

如果被檢索傳回的列包括在index列中,oracle将不執行第二步的處理(通過rowid通路表). 因為檢索資料儲存在索引中, 單單通路索引就可以完全滿足查詢結果.

下面sql隻需要index unique scan 操作.

select lodging

索引範圍查詢(index range scan)

适用于兩種情況:

1. 基于一個範圍的檢索

2. 基于非唯一性索引的檢索

例1:

where lodging like ‘m%';

where子句條件包括一系列值, oracle将通過索引範圍查詢的方式查詢lodging_pk . 由于索引範圍查詢将傳回一組值, 它的效率就要比索引唯一掃描

低一些.

例2:

where manager = ‘bill gates';

這個sql的執行分兩步, lodging$manager的索引範圍查詢(得到所有符合條件記錄的rowid) 和下一步同過rowid通路表得到lodging列的值. 由于lodging$manager是一個非唯一性的索引,資料庫不能對它執行索引唯一掃描.

由于sql傳回lodging列,而它并不存在于lodging$manager索引中, 是以在索引範圍查詢後會執行一個通過rowid通路表的操作.

where子句中, 如果索引列所對應的值的第一個字元由通配符(wildcard)開始, 索引将不被采用.

where manager like ‘％hanman';

在這種情況下，oracle将使用全表掃描.