使用insert插入大量数据的个人经验总结(页 1) - 软件学习交流 - 我爱电脑技术论坛打造最好的电脑自学交流论坛

黑侠发表于 2008-6-30 09:11

使用insert插入大量数据的个人经验总结

这篇论坛文章根据笔者的个人经验着重介绍了使用insert插入大量数据的常用技巧，更多内容请参考下文： 1r1?l1b#YM

在很多时候，我们会需要对一个表进行插入大量的数据，并且希望在尽可能短的时间内完成该工作，这里，和大家分享下我平时在做大量数据insert的一些经验。 6hqf:\-F5v#S
9MM:H3m"J&fQ3B
前提：在做insert数据之前，如果是非生产环境，请将表的索引和约束去掉，待insert完成后再建索引和约束。
?Ic"KRB,j+h })~
1.insert into tab1 select * from tab2; v;_y!l!|6^m8V
$W6T3c [s&J
commit;

这是最基础的insert语句，我们把tab2表中的数据insert到tab1表中。根据经验，千万级的数据可在1小时内完成。但是该方法产生的arch会非常快，需要关注归档的产生量，及时启动备份软件，避免arch目录撑爆。

2.alter table tab1 nologging;

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

commit;
n)Fq4Foo1p
alter table tab1 logging;

该方法会使得产生arch大大减少，并且在一定程度上提高时间，根据经验，千万级的数据可在45分钟内完成。但是请注意，该方法适合单进程的串行方式，如果当有多个进程同时运行时，后发起的进程会有enqueue的等待。注意此方法千万不能dataguard上用（不过要是在database已经force logging那也是不怕的，呵呵）！！ n+Pj~(Z(`)O J*Is

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

commit; ApP{;n4V&F7}a|
#[u2P(R-k P_a'`
对于select之后的语句是全表扫描的情况，我们可以加parallel的hint来提高其并发，这里需要注意的是最大并发度受到初始化参数parallel_max_servers的限制，并发的进程可以通过v$px_session查看，或者ps -ef |grep ora_p查看。 $a7_*Up\r] A0a
;y4Y T6gNK
+R.r D'@,[9^,R2Y;tR
4.alter session enable parallel dml;

insert /*+ parallel */ into tab1 select * from tab2; q,g8c2k"m

commit;

与方法2相反，并发的insert，尚未比较和方法2哪个效率更高（偶估计是方法2快），有测试过的朋友欢迎补充。 %sO;bl5r5Yw

5.insert into tab1 select * from tab2 partition (p1);
E}+oV,}8f7m A-v|
insert into tab1 select * from tab2 partition (p2);
)a8s`l3Iw0u!ko
insert into tab1 select * from tab2 partition (p3); UR"zsi

insert into tab1 select * from tab2 partition (p4); r:ue(aS3gw t)f

对于分区表可以利用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;
Dwn$?bx
v_col2 dtarray; 2I%?'dm {BH
.e:\L'_[Dbe\/S
v_col3 dtarray;

BEGIN l*s,I\~-o4V2^s

SELECT col1, col2, col3 BULK COLLECT

INTO v_col1, v_col2, v_col3 &u mPB,{qE0})N"\5^
k6@n Zf%Kps
FROM tab2; ~?_G%xE!G

FORALL i IN 1 .. v_col1.COUNT h0]n_[Z,i+r
5H)r'f1?5C0]9xS`b^
insert into tab1 WHERE tab1.col1 = v_col1;

END;

用批量绑定（bulk binding）的方式。当循环执行一个绑定变量的sql语句时候，在PL/SQL 和SQL引擎(engines)中，会发生大量的上下文切换(context switches）。使用bulk binding，能将数据批量的从plsql引擎传到sql引擎，从而减少上下文切换过程，提升效率。该方法比较适合于在线处理，不必停机。
&H@^r v'tlMi
7.sqlplus -s user/pwd< runlog.txt
qBq7G~1Pe"h{
set copycommit 2;

set arraysize 5000; $V:T)Q0qEM
m-G}&Sg6U:up
copy from user/pwd@sid -
J/m'AU#Zc;k3i7E
to user/pwd@sid - m;H~8Po-b1po)r S3U7H

insert tab1 using select * from tab2; 6[~yU8M

exit
*U(k*Mz4A5W5X/v
EOF
,Xna%]iqdVA
用copy的方法进行插入，注意此处insert没有into关键字。该方法的好处是可以设置copycommit和arrarysize来一起控制commit的频率，上面的方法是每10000行commit一次。

页: [1]

我爱电脑技术论坛's Archiver

使用insert插入大量数据的个人经验总结