如何加快建 index 索引 的時間

朋友在500w的表上建索引，半個小時都沒有結束。所以就討論如何提速。

一．先來看一下建立索引要做哪些操作：
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

有關索引概念性的東西，請參考我的Blog：

 索引 詳解

http://blog.csdn.net/tianlesoftware/archive/2010/03/05/5347098.aspx

二． 

自己也測試了下。測試環境：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

單位：s

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的爭用。

 

轉： http://blog.csdn.net/tianlesoftware/article/details/5664019