| 導購 | 订阅 | 在线投稿
分享
 
 
 

使用insert插入大量數據的個人經驗總結

來源:互聯網  2008-07-01 06:56:46  評論

這篇論壇文章根據筆者的個人經驗著重介紹了使用insert插入大量數據的常用技巧,更多內容請參考下文:

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

前提:在做insert數據之前,如果是非生産環境,請將表的索引和約束去掉,待insert完成後再建索引和約束。

1.insert into tab1 select * from tab2;

commit;

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

2.alter table tab1 nologging;

insert /*+ append */ into tab1 select * from tab2;

commit;

alter table tab1 logging;

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

3.insert into tab1 select /*+ parallel */ * from tab2;

commit;

對于select之後的語句是全表掃描的情況,我們可以加parallel的hint來提高其並發,這裏需要注意的是最大並發度受到初始化參數parallel_max_servers的限制,並發的進程可以通過v$px_session查看,或者ps -ef |grep ora_p查看。

4.alter session enable parallel dml;

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

commit;

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

5.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分鍾了。

6. 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引擎,從而減少上下文切換過程,提升效率。該方法比較適合于在線處理,不必停機。

7.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一次。

這篇論壇文章根據筆者的個人經驗著重介紹了使用insert插入大量數據的常用技巧,更多內容請參考下文: 在很多時候,我們會需要對一個表進行插入大量的數據,並且希望在盡可能短的時間內完成該工作,這裏,和大家分享下我平時在做大量數據insert的一些經驗。 前提:在做insert數據之前,如果是非生産環境,請將表的索引和約束去掉,待insert完成後再建索引和約束。 1.insert into tab1 select * from tab2; commit; 這是最基礎的insert語句,我們把tab2表中的數據insert到tab1表中。根據經驗,千萬級的數據可在1小時內完成。但是該方法産生的arch會非常快,需要關注歸檔的産生量,及時啓動備份軟件,避免arch目錄撐爆。 2.alter table tab1 nologging; insert /*+ append */ into tab1 select * from tab2; commit; alter table tab1 logging; 該方法會使得産生arch大大減少,並且在一定程度上提高時間,根據經驗,千萬級的數據可在45分鍾內完成。但是請注意,該方法適合單進程的串行方式,如果當有多個進程同時運行時,後發起的進程會有enqueue的等待。注意此方法千萬不能dataguard上用(不過要是在database已經force logging那也是不怕的,呵呵)!! 3.insert into tab1 select /*+ parallel */ * from tab2; commit; 對于select之後的語句是全表掃描的情況,我們可以加parallel的hint來提高其並發,這裏需要注意的是最大並發度受到初始化參數parallel_max_servers的限制,並發的進程可以通過v$px_session查看,或者ps -ef |grep ora_p查看。 4.alter session enable parallel dml; insert /*+ parallel */ into tab1 select * from tab2; commit; 與方法2相反,並發的insert,尚未比較和方法2哪個效率更高(偶估計是方法2快),有測試過的朋友歡迎補充。 5.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分鍾了。 6. 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引擎,從而減少上下文切換過程,提升效率。該方法比較適合于在線處理,不必停機。 7.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一次。
󰈣󰈤
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
王朝網路微信公眾號
微信掃碼關註本站公眾號 wangchaonetcn
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有