一、創建索引對執行計劃的影響

在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語句，是有可能從新創建的索引中獲得性能提升的。