創建ORACLE索引對ORACLE內部機制的影響
創建ORACLE索引可以提高數據庫的查詢效率，那么，創建ORACLE索引對ORACLE內部機制有什么影響呢？閱讀下文，您就可以找到答案。
創建索引不會改變已經運行的SQL的執行計劃。但是并不是說，創建索引不能給已經運行的SQL語句帶來性能的提升。
下面看一個比較特殊的例子：
SQL> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已創建。
SQL> CREATE TABLE TEST1 AS SELECT ROWNUM ID, ROWNUM FID, A.* FROM DBA_SYNONYMS A;
表已創建。
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;
已創建1616行。
SQL> INSERT INTO TEST1 SELECT * FROM TEST1;
已創建3232行。
SQL> INSERT INTO TEST1 SELECT * FROM TEST1;
已創建6464行。
SQL> INSERT INTO TEST1 SELECT * FROM TEST1;
已創建12928行。
SQL> INSERT INTO TEST1 SELECT * FROM TEST1;
已創建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
構造兩張表，TEST1的FID建立了參考TEST表ID列的外鍵。但是這里并沒有在外鍵列上創建ORACLE索引。
向TEST和TEST1表中填入一定數據量的數據，開始測試。這里測試的是刪除TEST表的執行時間。首先將TEST1用DELETE命令刪除，提交后計算刪除TEST表的時間，大約需要17秒，然后將數據回滾。
下面準備進行第二次刪除測試，所不同的是，在刪除操作開始后，馬上在另一個SESSION中給外鍵列增加索引，通過測試可以發現，幾乎在索引創建完的同時，***個SESSION就返回了結果，刪除需要的時間縮短到了3秒。
***個SESSION的刪除語句：
SQL> DELETE TEST;
已刪除6208行。
已用時間:? 00: 00: 03.00
第二個SESSION的索引創建語句：
SQL> CREATE INDEX IND_TEST1_FID ON TEST1(FID);
索引已創建
這個測試中索引的創建影響到了已經在運行的SQL語句，并明顯地提高了執行效率。這個現象和上一篇文章中描述的觀點并不沖突。對于用戶發出的SQL語句，Oracle的執行計劃是不變的，但是為了執行用戶發出的SQL語句，Oracle在內部做了大量的操作，包括權限的檢查、語法的檢查、目標對象是否存在，以及維護數據的完整性等等。這個例子中，用戶發出的SQL語句的執行計劃沒有改變，發生改變的是Oracle內部維護操作語句的執行計劃。
如果在***個SESSION執行DELETE操作的同時，通過下面的SQL語句檢查***個SESSION正在運行的語句，會發現下面的結果（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用來維護完整性的內部SQL。
回想一下我們的例子，建立了外鍵，但是沒有建立索引。當每刪除一條TEST的記錄，Oracle都要檢查這個主鍵是否在TEST1中被引用。由于沒有索引，Oracle只能通過全表掃描來尋找TEST1中的記錄。雖然TEST1沒有記錄，但是刪除TEST時使用的是DELETE而不是TRUNCATE，因此TEST1的高水位線并沒有下降，也就是說，每刪除一條TEST的記錄，都需要全表掃描一張擁有5萬條數據的表，這就是為什么那個DELETE操作執行很慢的原因。
而我們建立的索引正是加快了這個步驟，Oracle內部維護的SQL語句在索引可用后選擇了索引掃描，因此DELETE操作在索引創建后迅速返回。
【編輯推薦】
