Oracle SQL性能優化系列 (八)
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 LODGINGWHERE LODGING = ‘ROSE HILL’; 在內部 , 上述SQL將被分成兩步執行, 首先 , LODGING_PK 索引將通過索引唯一掃描的方式被訪問 , 獲得相對應的ROWID, 通過ROWID訪問表的方式 執行下一步檢索.假如被檢索返回的列包括在INDEX列中,ORACLE將不執行第二步的處理(通過ROWID訪問表). 因為檢索數據保存在索引中, 單單訪問索引就可以完全滿足查詢結果. 下面SQL只需要INDEX UNIQUE SCAN 操作. SELECT LODGINGFROM LODGINGWHERE LODGING = ‘ROSE HILL’; 索引范圍查詢(INDEX RANGE SCAN)適用于兩種情況:1. 基于一個范圍的檢索2. 基于非唯一性索引的檢索 例1: SELECT LODGINGFROM LODGINGWHERE LODGING LIKE ‘M%’; WHERE子句條件包括一系列值, ORACLE將通過索引范圍查詢的方式查詢LODGING_PK . 由于索引范圍查詢將返回一組值, 它的效率就要比索引唯一掃描低一些. 例2: SELECT LODGINGFROM LODGINGWHERE MANAGER = ‘BILL GATES’; 這個SQL的執行分兩步, LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID) 和下一步同過ROWID訪問表得到LODGING列的值. 由于LODGING$MANAGER是一個非唯一性的索引,數據庫不能對它執行索引唯一掃描. 由于SQL返回LODGING列,而它并不存在于LODGING$MANAGER索引中, 所以在索引范圍查詢后會執行一個通過ROWID訪問表的操作. WHERE子句中, 假如索引列所對應的值的第一個字符由通配符(WILDCARD)開始, 索引將不被采用. SELECT LODGINGFROM LODGINGWHERE MANAGER LIKE ‘％HANMAN’; 在這種情況下，ORACLE將使用全表掃描. (待續)
