一、創建索引對執行計劃的影響 在SQL開始執行之前，Oracle會確定SQL語句的執行計劃，并按照執行計劃的步驟訪問相應的表和索引。 一旦執行計劃確定下來，Oracle會按照這個執行計劃完成SQL語句的執行，在SQL語句執行開始之后建立的索引不會改變SQL語句的執行計劃。 因此，創建索引不會對執行計劃有任何的影響，也就不會對運行中的SQL語句有影響。下面通過一個例子簡單驗證一下： SQL> CREATE TABLE TEST (ID NUMBER, FID NUMBER, NAME VARCHAR2(30), BID_COLUMNS CHAR(2000)); Table created. SQL> INSERT INTO TEST VALUES (1, 0, 'OBJECT', '0'); 1 row created. SQL> INSERT INTO TEST VALUES (2, 1, 'TABLE', '0'); 1 row created. SQL> INSERT INTO TEST VALUES (3, 1, 'INDEX', '0'); 1 row created. SQL> INSERT INTO TEST VALUES (4, 1, 'VIEW', '0'); 1 row created. SQL> INSERT INTO TEST VALUES (5, 1, 'SYNONYM', '0'); 1 row created. SQL> INSERT INTO TEST VALUES (6, 1, 'SOURCE', '0'); 1 row created. SQL> INSERT INTO TEST SELECT 20000+ROWNUM, 2, TABLE_NAME, '0' FROM DBA_TABLES; 874 rows created. SQL> INSERT INTO TEST SELECT 30000+ROWNUM, 3, INDEX_NAME, '0' FROM DBA_INDEXES; 1074 rows created. SQL> INSERT INTO TEST SELECT 40000+ROWNUM, 4, VIEW_NAME, '0' FROM DBA_VIEWS; 2929 rows created. SQL> INSERT INTO TEST SELECT 50000+ROWNUM, 5, TABLE_NAME, '0' FROM DBA_SYNONYMS; 2437 rows created. SQL> INSERT INTO TEST SELECT 60000+ROWNUM, 6, NAME, '0' FROM DBA_SOURCE; 99717 rows created. SQL> COMMIT; Commit complete. SQL> set timing on SQL> SELECT COUNT(*) FROM TEST 2 START WITH ID = 1 3 CONNECT BY PRIOR ID = FID; COUNT(*) ---------- 107037 Elapsed: 00:02:03.84 構造一個樹狀查詢，然后記錄這個樹狀查詢的運行時間。 SQL> SELECT COUNT(*) FROM TEST 2 START WITH ID = 1 3 CONNECT BY PRIOR ID = FID; COUNT(*) ---------- 107037 Elapsed: 00:05:26.15 再次運行查詢，在查詢運行開始，馬上在另一個session創建索引。通過觀察執行時間可以發現，創建索引不會對運行中的SQL語句帶來性能提升，而且很可能由于系統資源的爭用造成查詢速度變慢。假如在IO分布的比較合理的系統中，可以看到，創建索引可以很快完成，而且隨后執行同樣的查詢由于會使用索引，也會很快的返回結構，但是索引的創建不會加快已經處于運行狀態的語句的速度。 SESSION2: SQL> SET TIMING ON SQL> CREATE INDEX IND_TEST_ID ON TEST(ID) TABLESPACE USERS; 索引已創建。 已用時間:000: 01: 56.92 SQL> CREATE INDEX IND_TEST_FID ON TEST(FID) TABLESPACE USERS; 索引已創建。 已用時間: 00: 02: 00.57 建立索引后，同樣的查詢速度得到明顯的提升。 SQL> SELECT COUNT(*) FROM TEST 2 START WITH ID = 1 3 CONNECT BY PRIOR ID = FID; COUNT(*) ---------- 107037 已用時間: 00: 01: 02.11 上面建立兩個索引的語句和查詢語句是在單獨的SESSION2上運行的。SESSION2上的三個操作——創建兩個索引和執行相同的查詢語句——都執行完成了，而第一個會話的的運行結果仍然沒有返回。 二、創建索引對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列的外鍵。但是這里并沒有在外鍵列上建立索引。 向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操作在索引創建后迅速返回。 三、小結 創建索引對于用戶已發出的正在運行的SQL不會帶來性能的提升。這是由于用戶執行的語句要按照執行計劃來運行，而執行計劃在運行開始的時候就確定下來了，且不會在SQL語句的運行過程中發生變化。 對于SQL執行過程中，Oracle內部執行的用于維護的SQL語句，是有可能從新創建的索引中獲得性能提升的。
