Oracle insert大量数据经验之谈

向右看齐 2021-06-26 16:06 409阅读 0赞

在很多时候，我们会需要对一个表进行插入大量的数据，并且希望在尽可能短的时间内完成该工作，这里，和大家分享下我平时在做大量数据`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`一次。