Oracle建立索引選擇合適的可選項及效率問題

有些新手在中建立索引時往往不會使用可選項。其實，有時候在合適的場合使用一些可選項，可以提高索引的建立速度。如為了大批次匯入資料，我們往往會先取消索引其以提高插入的速度。然後等資料匯入完畢後再重新建立索引。在這個過程中如果能夠採用一些可選項，則可以縮短索引建立的時間。在Oracle資料庫中提供了豐富的可選項。我們常用的可選項主要有以下這些。

　　可選項一：NOSORT，記錄排序可選項

　　預設情況下，在表中建立索引的時候，會對錶中的記錄進行排序，排序成功後再建立索引。但是當記錄比較多的是，這個排序作業會佔用比較多的時間，這也就增加了索引建立的時間(排序作業是在索引建立作業中完成)。有時候，我們匯入資料的時候，如採用insert into 語句插入資料過程中同時採用Order by子句對索引欄位進行了排序。此時如果在索引建立過程中再進行排序的話，就有點脫褲子放屁，多此一舉了。為此在重新建立索引時，如果表中的資料已經排好序了(按索引欄位排序)，那麼在建立索引時就不需要為此重新排序。此時在建立索引時，員就可以使用NOSORT可選項，告訴資料庫系統不需要對錶中當記錄進行重新排序了。

　　採用了這個選項之後，如果表中的記錄已經按順序排列，那麼在重新建立索引的時候，就不會重新排序，可以提高索引建立的時間，節省記憶體中的排序快取空間。相反，如果表中的記錄是不按索引關鍵字排序的話，那麼此時採用NOSORT關鍵字的話，系統就會提示錯誤資訊，並拒絕建立索引。所以在使用NOSORT可選項的時候，資料庫管理員儘管放心大膽的使用。因為其實在不能夠使用這個選項的時候，資料庫也會明確的告知。為此其副作用就比較少，資料庫管理員只需要把這個可選項去掉然後重新執行一次即可。不過這裡需要注意的是，如果表中的記錄比較少的話，那麼使用NOSORT選項的效果並不是很明顯。當採用insert into批次匯入資料，並在這個過程中採用了Order by子句對索引關鍵字進行了排序的話，則此時採用NOSORT選項的話，往往能夠起到比較好的效果。

　　可選項二：NOLOGGING，是否需要記錄日誌資訊

　　在建立索引的時候，系統會把相關的資訊到日誌資訊中去。如果表中的記錄比較多，則需要一一的把這些資訊記錄到日誌檔案中，這顯然會讓資料庫增加很大的工作量。從而增加索引建立的時間。為此在建立索引的過程中，如果有必要時，我們可以採用NOLOGGING選項，讓資料庫在建立索引的過程中，不產生任何重做日誌資訊。此時當表中的記錄比較多時，就可以明顯提高速度。

　　但是預設情況下，資料庫在在建立索引時，是不採用這個選項的，即會把相關的資訊儲存到重做日誌中去。這雖然降低了索引建立的效率，但是如果遇到什麼意外的話，卻可以利用重做日誌來進行恢復。所以，此時資料庫管理員就比較難以抉擇了。一方面是資料的安全，另一方面是索引建立的速度。根據筆者的經驗，只要資料庫比較穩定，而資料庫中約束機制又比較完善的話，那麼在建立索引的過程中一般不會出現問題，可以放心大膽的使用這個可選項。

　　但是如果資料庫已經使用了好幾年了。後來因為某種原因需要重建索引。在這種情況下，由於資料庫使用過程中很多因素資料庫管理員無法控制。此時為這種型別的資料庫建立索引時，為了保險起見還是不要採用這個選項好。因為此時遇到錯誤的機率相對來說會搞一點。為此此時犧牲一下索引建立的速率，而提高資料的安全性還是有必要的。萬一遇到什麼問題時，可以透過重做日誌來及時的恢復資料，為使用者減少損失。

　　可選項三：COMPUTE STATISTICS，是否生成統計資訊

　　如果管理員在建立索引時採用了這個選項，則資料庫將在建立索引的過程中以非常小的代價直接生成關於索引的相關統計資訊，然後把這些資訊儲存在資料字典中。這就可以避免以後對索引進行分析統計，而且最佳化器在最佳化SQL語句的時候可以隨機使用這些統計資訊，以確定是否生成使用該索引的執行計劃。通常情況下，在生成索引的過程中統計索引的相關資訊，其所花的代價是最小的。無論從時間上，還是從硬體資源的耗費上，都是非常小的。所以，在建立索引的過程中統計相關的索引資訊是非常有用的。

　　但是預設情況下，資料庫是不採用這個選項的。這主要是因為一些事物處理系統，索引的資訊是經常需要發生變化的。如果在索引建立的過程中統計了相關資訊。這些資訊隨著索引的調整等等原因會很快的過時。所以說，其在預設情況下沒有采用這個選項。可見這個選項並不是在任何情況下都能夠起到效果。但是如果這個資料庫系統是一個決策支援系統。其資料、索引等等在一段時間內基本上是穩定不變的。此時在建立索引時可以使用這個選項。如此的話，在生成索引時可以以最小的代價生成這些統計資訊，方便最佳化器使用。筆者在部署的時候，對於事務型的資料庫系統，一般不會啟用這個選項。但是對於一些決策性的資料庫系統或者資料倉儲中，建立索引時則筆者喜歡採用這個選項。這有助於提高資料庫的效能。因為最佳化器在生成執行計劃時，可以直接採用這個統計資訊。所以，資料庫能夠在最短的時間內確定需要採用的執行計劃。而且在執行計劃制定中參考了這個索引統計資訊，為此所生成的執行計劃在同等條件下可能更加的合理。

　　可選項四：ONLINE，DML操作與建立索引操作是否可以同時進行

　　預設情況下，資料庫系統是不允許DML操作與建立索引的操作同時進行的。也就是說，在建立索引的過程中，是不允許其他使用者對其所涉及的表進行任何的DML操作。這主要是因為對基礎表進行DML操作時，會對基礎表進行加鎖。所以在基礎表上的DDL事務沒有遞交之前，即沒有對基礎表進行解鎖之前，是無法對這基礎表建立索引的。反之亦然。顯然此時資料庫沒有采用這個ONLIE選項，繼之DML操作與建立索引操作同時進行，主要是從建立索引的效率出發的。防止因為兩個作業相互衝突，從而延長某個作業的執行時間。

　　但是有時會我們必須允許他們進行同時操作。如使用者可能一刻都不能夠離開資料庫系統，需要時時刻刻對資料庫基礎表進行DML操作。而此時由於某些原因，資料庫管理員又需要重新建立索引時，那麼不得不在建立索引的語句中加入這個ONLINE選項。讓他們同時執行。此時雖然可能會延長索引建立作業的時間，但是可以保障使用者DML操作能夠正常進行。有時候犧牲這個代價是值得的。使用者是不能夠等的，而我們資料庫管理員則可以勉強的等一會兒。

　　當然，如果使用者對於這個DML操作及時性沒有這麼高。如資料庫管理員在晚上員工沒有使用資料庫時建立索引時，則可以不帶這個選項。在限制使用者對基礎表進行DML操作的同時，提高資料庫建立索引的效率。

　　可選項五：PARALLEL，多服務程式建立索引

　　預設情況下，Oracle資料庫系統不採用這個選項。這並不是說這個選項不可用，而是因為大多數情況下企業部署Oracle資料庫時所採用的資料庫伺服器往往只有單個CPU。此時資料庫系統是用一個服務程式來建立索引的。

　　如果企業的伺服器有多個CPU的話，則可以在建立索引時採用這個選項。因為只要採用了這個選項，則資料庫就會使用多個服務程式來並行的建立索引，以提高索引建立的速度。為此，在同等條件下，多服務並行建立進索引並單服務建立索引速度要快的多。所以如果伺服器中有多個CPU，而且需要建立的索引比較多或者基礎表中記錄比較多的話，則採用這個選項能夠大幅度的提高索引的建立效率。

　　故筆者建議，如果採用多CPU的伺服器時，最好在建立索引時使用這個選項。不能夠浪費了伺服器的CPU呀。不然的話，多CPU伺服器的優勢就體現不出來了。為此採用這個選項，也是物盡其用。希望本文講到的內容對大家能有所幫助。

Oracle 加快建立索引的時間

Oracle 加快新建 index 索引的時間，包括建立索引要做哪些操作、測試環境、檢視錶的資料量、檢視現有索引、刪除索引、預設方式建立索引、Nologging parallel 模式、Parallel 模式等。

一、建立索引要做哪些操作：

1、把 index key 的data 讀到記憶體

如果 data 沒在 db_cache 中，這時候很容易有大量的 db file scatter read wait

2、對 index key 的 data 作排序

sort_area_size  或者 pga_aggregate_target 不夠大的情況下，需要做 disk sort ，會有大量的 driect path read/write ，另外，消耗大量 CPU Time

3、建立新的 index segment ，把排過序的 index data 寫到所建立的 index segment 裡面

如果 index 很大，那麼，有時也會有 redo log 相關等待，如：log buffer space 、log file sync 、 log file parallel write 等，所以在建大表索引時，可以增大 pga ，增大 temp tablepace ，並且用 nologging 或並行選項。如：

以下為引用內容： 

create index idx_logs on logs(time) nologging parallel 4;

並行度一般看 CPU 個數。當然在 CPU 比較空閒的情況下可以多並行幾個。對於單 CPU 不建議用並行，這樣反而會增加建立時間。也可以根據 v$session_wait 的資料，做針對性的 tuning ，這樣可以降低點時間。

[補充]

檢視cpu 資訊：more /proc/cpuinfo

檢視記憶體資訊：more /proc/meminfo

檢視作業系統資訊：more /etc/issue

二、測試

測試環境：Oracle 11g R2, win7 64bit 、CPU T6670 2.2G 雙核、記憶體：4G 。

1、檢視錶的資料量

以下為引用內容： 

SQL> select count(*) from custaddr;

COUNT(*)

----------

7230464

2、檢視現有索引

以下為引用內容： 

SQL> select index_name,index_type from user_indexes where table_name='CUSTADDR';

INDEX_NAME INDEX_TYPE

------------------------------

PK_CUSTADDR_TP_723 NORMAL

IX_CUSTADDR_ADDRABB_TP NORMAL

IX_CUSTADDR_TEAMID_TP NORMAL

IX_CUSTADDR_CUSTID_TP NORMAL

IX_CUSTADDR_COMPABB_TP NORMAL

IX_CUSTADDR_AREACODE NORMAL

IX_CUSTADDR_ADDR_TP NORMAL

已選擇7行。

3、刪除索引：IX_CUSTADDR_CUSTID_TP

以下為引用內容： 

SQL> drop index IX_CUSTADDR_CUSTID_TP ;

索引已刪除。

4、預設方式建立索引

以下為引用內容： 

SQL> SET timing on;

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID );

索引已建立。

已用時間: 00: 00: 48.37

5、用 nologging 模式

以下為引用內容： 

SQL> drop index IX_CUSTADDR_CUSTID_TP ;

索引已刪除。

已用時間: 00: 00: 00.09

以下為引用內容： 

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID ) NOLOGGING;

索引已建立。

已用時間: 00: 00: 34.46

6、Nologging+ parallel 模式

以下為引用內容： 

SQL> drop index IX_CUSTADDR_CUSTID_TP ;

索引已刪除。

已用時間: 00: 00: 00.17

以下為引用內容： 

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID ) NOLOGGING PARALLEL 2;

索引已建立。

已用時間: 00: 00: 52.56

以下為引用內容： 

SQL> drop index IX_CUSTADDR_CUSTID_TP ;

索引已刪除。

已用時間: 00: 00: 00.07

以下為引用內容： 

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID ) NOLOGGING PARALLEL 4;

索引已建立。

已用時間: 00: 00: 53.44

看來在單 CPU 上，並行效果還不好.

7、Parallel 模式

以下為引用內容： 

SQL> drop index IX_CUSTADDR_CUSTID_TP ;

索引已刪除。

已用時間: 00: 00: 00.02

以下為引用內容： 

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID ) PARALLEL 2;

索引已建立。

已用時間: 00: 00: 49.97

以下為引用內容： 

SQL> drop index IX_CUSTADDR_CUSTID_TP ;

索引已刪除。

已用時間: 00: 00: 00.02

以下為引用內容： 

SQL> CREATE INDEX IX_CUSTADDR_CUSTID_TP ON CUSTADDR (CUSTID ) PARALLEL 4;

索引已建立。

已用時間: 00: 00: 50.25

從上面的測試資料可以看出，700萬的資料，建索引，也在1分鐘以內。 但是並行在單 CPU 上效果不明顯，而且比光使用 NOLOGGING 還要慢，因為出現資源爭用了，可能是 CPU 的爭用，也可能是 I/O 的爭用。

1.select /*+ PARALLEL(a, 5) */count(*) from xt.test1 a,xt.test2 b where a.xh = b.xh

2.select /*+ PARALLEL(a, 5)  PARALLEL(b, 5) */count(*) from xt.test1 a,xt.test2 b where a.xh = b.xh