25. 用索引提高效率
索引是表的一個(gè)概念部分,用來(lái)提高檢索數(shù)據(jù)的效率. 實(shí)際上,ORACLE使用了一個(gè)復(fù)雜的自平衡B-tree結(jié)構(gòu). 通常,通過(guò)索引查詢數(shù)據(jù)比全表掃描要快. 當(dāng)ORACLE找出執(zhí)行查詢和Update語(yǔ)句的最佳路徑時(shí), ORACLE優(yōu)化器將使用索引. 同樣在聯(lián)結(jié)多個(gè)表時(shí)使用索引也可以提高效率. 另一個(gè)使用索引的好處是,它提供了主鍵(primary key)的唯一性驗(yàn)證.
除了那些LONG或LONG RAW數(shù)據(jù)類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當(dāng)然,你也會(huì)發(fā)現(xiàn), 在掃描小表時(shí),使用索引同樣能提高效率.
雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價(jià). 索引需要空間來(lái)
存儲(chǔ),也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時(shí), 索引本身也會(huì)被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤I/O . 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理,那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢.
譯者按:
定期的重構(gòu)索引是有必要的.
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
26. 索引的操作
ORACLE對(duì)索引有兩種訪問(wèn)模式.
索引唯一掃描 ( INDEX UNIQUE SCAN)
大多數(shù)情況下, 優(yōu)化器通過(guò)WHERE子句訪問(wèn)INDEX.
例如:
表LODGING有兩個(gè)索引 : 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER.
SELECT *
FROM LODGING
WHERE LODGING = ‘ROSE HILL’;
在內(nèi)部 , 上述SQL將被分成兩步執(zhí)行, 首先 , LODGING_PK 索引將通過(guò)索引唯一掃描的方式被訪問(wèn) , 獲得相對(duì)應(yīng)的ROWID, 通過(guò)ROWID訪問(wèn)表的方式 執(zhí)行下一步檢索.
如果被檢索返回的列包括在INDEX列中,ORACLE將不執(zhí)行第二步的處理(通過(guò)ROWID訪問(wèn)表). 因?yàn)闄z索數(shù)據(jù)保存在索引中, 單單訪問(wèn)索引就可以完全滿足查詢結(jié)果.
下面SQL只需要INDEX UNIQUE SCAN 操作.
SELECT LODGING
FROM LODGING
WHERE LODGING = ‘ROSE HILL’;
索引范圍查詢(INDEX RANGE SCAN)
適用于兩種情況:
1. 基于一個(gè)范圍的檢索
2. 基于非唯一性索引的檢索
例1:
SELECT LODGING
FROM LODGING
WHERE LODGING LIKE ‘M%’;
WHERE子句條件包括一系列值, ORACLE將通過(guò)索引范圍查詢的方式查詢LODGING_PK . 由于索引范圍查詢將返回一組值, 它的效率就要比索引唯一掃描
低一些.
例2:
SELECT LODGING
FROM LODGING
WHERE MANAGER = ‘BILL GATES’;
這個(gè)SQL的執(zhí)行分兩步, LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID) 和下一步同過(guò)ROWID訪問(wèn)表得到LODGING列的值. 由于LODGING$MANAGER是一個(gè)非唯一性的索引,數(shù)據(jù)庫(kù)不能對(duì)它執(zhí)行索引唯一掃描.
由于SQL返回LODGING列,而它并不存在于LODGING$MANAGER索引中, 所以在索引范圍查詢后會(huì)執(zhí)行一個(gè)通過(guò)ROWID訪問(wèn)表的操作.
WHERE子句中, 如果索引列所對(duì)應(yīng)的值的第一個(gè)字符由通配符(WILDCARD)開始, 索引將不被采用.
SELECT LODGING
FROM LODGING
WHERE MANAGER LIKE ‘％HANMAN’;
在這種情況下，ORACLE將使用全表掃描.
(待續(xù))