創(chuàng)建ORACLE索引對(duì)ORACLE內(nèi)部機(jī)制的影響
創(chuàng)建ORACLE索引可以提高數(shù)據(jù)庫(kù)的查詢(xún)效率，那么，創(chuàng)建ORACLE索引對(duì)ORACLE內(nèi)部機(jī)制有什么影響呢？閱讀下文，您就可以找到答案。
創(chuàng)建索引不會(huì)改變已經(jīng)運(yùn)行的SQL的執(zhí)行計(jì)劃。但是并不是說(shuō)，創(chuàng)建索引不能給已經(jīng)運(yùn)行的SQL語(yǔ)句帶來(lái)性能的提升。
下面看一個(gè)比較特殊的例子：
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行。
已用時(shí)間: 00: 00: 17.03
SQL> ROLLBACK;
回退已完成。
已用時(shí)間: 00: 00: 00.06
構(gòu)造兩張表，TEST1的FID建立了參考TEST表ID列的外鍵。但是這里并沒(méi)有在外鍵列上創(chuàng)建ORACLE索引。
向TEST和TEST1表中填入一定數(shù)據(jù)量的數(shù)據(jù)，開(kāi)始測(cè)試。這里測(cè)試的是刪除TEST表的執(zhí)行時(shí)間。首先將TEST1用DELETE命令刪除，提交后計(jì)算刪除TEST表的時(shí)間，大約需要17秒，然后將數(shù)據(jù)回滾。
下面準(zhǔn)備進(jìn)行第二次刪除測(cè)試，所不同的是，在刪除操作開(kāi)始后，馬上在另一個(gè)SESSION中給外鍵列增加索引，通過(guò)測(cè)試可以發(fā)現(xiàn)，幾乎在索引創(chuàng)建完的同時(shí)，***個(gè)SESSION就返回了結(jié)果，刪除需要的時(shí)間縮短到了3秒。
***個(gè)SESSION的刪除語(yǔ)句：
SQL> DELETE TEST;
已刪除6208行。
已用時(shí)間:? 00: 00: 03.00
第二個(gè)SESSION的索引創(chuàng)建語(yǔ)句：
SQL> CREATE INDEX IND_TEST1_FID ON TEST1(FID);
索引已創(chuàng)建
這個(gè)測(cè)試中索引的創(chuàng)建影響到了已經(jīng)在運(yùn)行的SQL語(yǔ)句，并明顯地提高了執(zhí)行效率。這個(gè)現(xiàn)象和上一篇文章中描述的觀點(diǎn)并不沖突。對(duì)于用戶(hù)發(fā)出的SQL語(yǔ)句，Oracle的執(zhí)行計(jì)劃是不變的，但是為了執(zhí)行用戶(hù)發(fā)出的SQL語(yǔ)句，Oracle在內(nèi)部做了大量的操作，包括權(quán)限的檢查、語(yǔ)法的檢查、目標(biāo)對(duì)象是否存在，以及維護(hù)數(shù)據(jù)的完整性等等。這個(gè)例子中，用戶(hù)發(fā)出的SQL語(yǔ)句的執(zhí)行計(jì)劃沒(méi)有改變，發(fā)生改變的是Oracle內(nèi)部維護(hù)操作語(yǔ)句的執(zhí)行計(jì)劃。
如果在***個(gè)SESSION執(zhí)行DELETE操作的同時(shí)，通過(guò)下面的SQL語(yǔ)句檢查***個(gè)SESSION正在運(yùn)行的語(yǔ)句，會(huì)發(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
這個(gè)SQL語(yǔ)句就是Oracle用來(lái)維護(hù)完整性的內(nèi)部SQL。
回想一下我們的例子，建立了外鍵，但是沒(méi)有建立索引。當(dāng)每刪除一條TEST的記錄，Oracle都要檢查這個(gè)主鍵是否在TEST1中被引用。由于沒(méi)有索引，Oracle只能通過(guò)全表掃描來(lái)尋找TEST1中的記錄。雖然TEST1沒(méi)有記錄，但是刪除TEST時(shí)使用的是DELETE而不是TRUNCATE，因此TEST1的高水位線(xiàn)并沒(méi)有下降，也就是說(shuō)，每刪除一條TEST的記錄，都需要全表掃描一張擁有5萬(wàn)條數(shù)據(jù)的表，這就是為什么那個(gè)DELETE操作執(zhí)行很慢的原因。
而我們建立的索引正是加快了這個(gè)步驟，Oracle內(nèi)部維護(hù)的SQL語(yǔ)句在索引可用后選擇了索引掃描，因此DELETE操作在索引創(chuàng)建后迅速返回。
【編輯推薦】
C#連接Oracle數(shù)據(jù)庫(kù)查詢(xún)數(shù)據(jù)
Oracle數(shù)據(jù)庫(kù)備份的三個(gè)常見(jiàn)誤區(qū)
