創(chuàng)建ORACLE索引可以提高數(shù)據(jù)庫的查詢效率，那么，創(chuàng)建ORACLE索引對ORACLE內(nèi)部機制有什么影響呢？閱讀下文，您就可以找到答案。

創(chuàng)建索引不會改變已經(jīng)運行的SQL的執(zhí)行計劃。但是并不是說，創(chuàng)建索引不能給已經(jīng)運行的SQL語句帶來性能的提升。

下面看一個比較特殊的例子：

SQL> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已創(chuàng)建。

SQL> CREATE TABLE TEST1 AS SELECT ROWNUM ID, ROWNUM FID, A.* FROM DBA_SYNONYMS A;

表已創(chuàng)建。

SQL> ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (ID);

表已更改。

SQL> ALTER TABLE TEST1 ADD CONSTRAINT FK_TEST1_FID FOREIGN KEY (FID) REFERENCES TEST(ID);

表已更改。

SQL> INSERT INTO TEST1 SELECT * FROM TEST1;

已創(chuàng)建1616行。

SQL> INSERT INTO TEST1 SELECT * FROM TEST1;

已創(chuàng)建3232行。

SQL> INSERT INTO TEST1 SELECT * FROM TEST1;

已創(chuàng)建6464行。

SQL> INSERT INTO TEST1 SELECT * FROM TEST1;

已創(chuàng)建12928行。

SQL> INSERT INTO TEST1 SELECT * FROM TEST1;

已創(chuàng)建25856行。

SQL> COMMIT;

提交完成。

SQL> DELETE TEST1;

已刪除51712行。

SQL> COMMIT;

提交完成。

SQL> SET TIMING ON

SQL> DELETE TEST;

已刪除6208行。

已用時間: 00: 00: 17.03

SQL> ROLLBACK;

回退已完成。

已用時間: 00: 00: 00.06

構(gòu)造兩張表，TEST1的FID建立了參考TEST表ID列的外鍵。但是這里并沒有在外鍵列上創(chuàng)建ORACLE索引。

向TEST和TEST1表中填入一定數(shù)據(jù)量的數(shù)據(jù)，開始測試。這里測試的是刪除TEST表的執(zhí)行時間。首先將TEST1用DELETE命令刪除，提交后計算刪除TEST表的時間，大約需要17秒，然后將數(shù)據(jù)回滾。

下面準(zhǔn)備進行第二次刪除測試，所不同的是，在刪除操作開始后，馬上在另一個SESSION中給外鍵列增加索引，通過測試可以發(fā)現(xiàn)，幾乎在索引創(chuàng)建完的同時，***個SESSION就返回了結(jié)果，刪除需要的時間縮短到了3秒。

***個SESSION的刪除語句：

SQL> DELETE TEST;

已刪除6208行。

已用時間:? 00: 00: 03.00

第二個SESSION的索引創(chuàng)建語句：

SQL> CREATE INDEX IND_TEST1_FID ON TEST1(FID);

索引已創(chuàng)建

這個測試中索引的創(chuàng)建影響到了已經(jīng)在運行的SQL語句，并明顯地提高了執(zhí)行效率。這個現(xiàn)象和上一篇文章中描述的觀點并不沖突。對于用戶發(fā)出的SQL語句，Oracle的執(zhí)行計劃是不變的，但是為了執(zhí)行用戶發(fā)出的SQL語句，Oracle在內(nèi)部做了大量的操作，包括權(quán)限的檢查、語法的檢查、目標(biāo)對象是否存在，以及維護數(shù)據(jù)的完整性等等。這個例子中，用戶發(fā)出的SQL語句的執(zhí)行計劃沒有改變，發(fā)生改變的是Oracle內(nèi)部維護操作語句的執(zhí)行計劃。

如果在***個SESSION執(zhí)行DELETE操作的同時，通過下面的SQL語句檢查***個SESSION正在運行的語句，會發(fā)現(xiàn)下面的結(jié)果（9i及以前版本，如果是10g，則只能看到DELETE TEST）。

SQL> SELECT SQL_TEXT FROM V$SESSION A, V$SQL B  
 
2 WHERE A.SQL_HASH_VALUE = B.HASH_VALUE  
 
3 AND A.SQL_ADDRESS = B.ADDRESS  
 
4 AND A.SID = 17;  
 
SQL_TEXT  
 
----------------------------------------------------------------------------  
 
select /**//*+ all_rows */ count(1) from "YANGTK"."TEST1" where "FID" = :1  

這個SQL語句就是Oracle用來維護完整性的內(nèi)部SQL。

回想一下我們的例子，建立了外鍵，但是沒有建立索引。當(dāng)每刪除一條TEST的記錄，Oracle都要檢查這個主鍵是否在TEST1中被引用。由于沒有索引，Oracle只能通過全表掃描來尋找TEST1中的記錄。雖然TEST1沒有記錄，但是刪除TEST時使用的是DELETE而不是TRUNCATE，因此TEST1的高水位線并沒有下降，也就是說，每刪除一條TEST的記錄，都需要全表掃描一張擁有5萬條數(shù)據(jù)的表，這就是為什么那個DELETE操作執(zhí)行很慢的原因。

而我們建立的索引正是加快了這個步驟，Oracle內(nèi)部維護的SQL語句在索引可用后選擇了索引掃描，因此DELETE操作在索引創(chuàng)建后迅速返回。

【編輯推薦】

創(chuàng)建Oracle索引的方法

C#連接Oracle數(shù)據(jù)庫查詢數(shù)據(jù)

Oracle數(shù)據(jù)庫備份的三個常見誤區(qū)

Oracle自動備份數(shù)據(jù)庫的三種方式

oracle RMAN備份的優(yōu)化