Oracle insert大量資料經驗之談

在很多時候，我們會需要對一個表進行插入大量的資料，並且希望在儘可能短的時間內完成該工作，這裡，和大家分享下我平時在做大量資料insert的一些經驗。

前提：在做insert資料之前，如果是非生產環境，請將表的索引和約束去掉，待insert完成後再建索引和約束。

insert into tab1 select * from tab2; 
commit;

這是最基礎的insert語句，我們把tab2表中的資料insert到tab1表中。根據經驗，千萬級的資料可在1小時內完成。但是該方法產生的arch會非常快，需要關注歸檔的產生量，及時啟動備份軟體，避免arch目錄撐爆。

　　alter table tab1 nologging; 
　　insert /*+ append */ into tab1 select * from tab2; 
　　commit; 
　　alter table tab1 logging;

該方法會使得產生arch大大減少，並且在一定程度上提高時間，根據經驗，千萬級的資料可在45分鐘內完成。但是請注意，該方法適合單程式的序列方式，如果當有多個程式同時執行時，後發起的程式會有enqueue的等待。注意此方法千萬不能dataguard上用(不過要是在database已經force logging那也是不怕的，呵呵)!!

insert into tab1 select /*+ parallel */ * from tab2; 
commit;

對於select之後的語句是全表掃描的情況，我們可以加parallel的hint來提高其併發，這裡需要注意的是最大併發度受到初始化引數parallel_max_servers的限制，併發的程式可以通過v$px_session檢視，或者ps -ef |grep ora_p檢視。

alter session enable parallel dml; 
insert /*+ parallel */ into tab1 select * from tab2; 
commit;

與方法2相反，併發的insert，尚未比較和方法2哪個效率更高(偶估計是方法2快)，有測試過的朋友歡迎補充。

　　insert into tab1 select * from tab2 partition (p1); 
　　insert into tab1 select * from tab2 partition (p2); 
　　insert into tab1 select * from tab2 partition (p3); 
　　insert into tab1 select * from tab2 partition (p4);

對於分割槽表可以利用tab1進行多個程式的併發insert，分割槽越多，可以啟動的程式越多。我曾經試過insert 2.6億行記錄的一個表，8個分割槽，8個程式，如果用方法2，單個程式完成可能要40分鐘，但是由於是有8個分割槽8個程式，後發程式有enqueue，所以因此需要的時間為40分鐘×8;但是如果用方法5，雖然單個程式需要110分鐘，但是由於能夠併發程式執行，所以總共需要的時間就約為110分鐘了。

　　DECLARE 
　　TYPE dtarray IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; 
　　v_col1 dtarray; 
　　v_col2 dtarray; 
　　v_col3 dtarray; 
　　BEGIN 
　　SELECT col1, col2, col3 BULK COLLECT 
　　INTO v_col1, v_col2, v_col3 
　　FROM tab2; 
　　FORALL i IN 1 .. v_col1.COUNT 
　　insert into tab1 WHERE tab1.col1 = v_col1; 
　　END;

用批量繫結(bulk binding)的方式。當迴圈執行一個繫結變數的sql語句時候，在PL/SQL 和SQL引擎(engines)中，會發生大量的上下文切換(context switches)。使用bulk binding，能將資料批量的從plsql引擎傳到sql引擎，從而減少上下文切換過程，提升效率。該方法比較適合於線上處理，不必停機。

　　sqlplus -s user/pwd< runlog.txt 
　　set copycommit 2; 
　　set arraysize 5000; 
　　copy from user/pwd@sid - 
　　to user/pwd@sid - 
　　insert tab1 using select * from tab2; 
　　exit 
　　EOF

用copy的方法進行插入，注意此處insert沒有into關鍵字。該方法的好處是可以設定copycommit和arrarysize來一起控制commit的頻率，上面的方法是每10000行commit一次。