建立索引對SQL語句執行的影響

來源:互聯網
上載者:User

一、建立索引對執行計畫的影響

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



相關文章

Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

Starter Package

SSD Cloud server and data transfer for only $2.50 a month

Get Started >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。