25. 用索引提高效率
索引是表的一個概念部分,用來提高檢索數據的效率. 實際上,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
from lodging
where lodging = ‘rose hill’;
索引范圍查詢(index range scan)
適用于兩種情況:
1. 基于一個范圍的檢索
2. 基于非唯一性索引的檢索
例1:
select lodging
from lodging
where lodging like ‘m%’;
where子句條件包括一系列值, oracle將通過索引范圍查詢的方式查詢lodging_pk . 由于索引范圍查詢將返回一組值, 它的效率就要比索引唯一掃描
低一些.
例2:
select lodging
from lodging
where manager = ‘bill gates’;
這個sql的執行分兩步, lodging$manager的索引范圍查詢(得到所有符合條件記錄的rowid) 和下一步同過rowid訪問表得到lodging列的值. 由于lodging$manager是一個非唯一性的索引,數據庫不能對它執行索引唯一掃描.
由于sql返回lodging列,而它并不存在于lodging$manager索引中, 所以在索引范圍查詢后會執行一個通過rowid訪問表的操作.
where子句中, 如果索引列所對應的值的第一個字符由通配符(wildcard)開始, 索引將不被采用.
select lodging
from lodging
where manager like ‘％hanman’;
在這種情況下，oracle將使用全表掃描.
(待續)
