insert

Oracleinsert⼤量数据经验之谈

在很多时候，我们会需要对⼀个表进⾏插⼊⼤量的数据，并且希望在尽可能短的时间内完成该⼯作，这⾥，和⼤家分享下我平时在做⼤量数据insert的⼀些经验。

前提：在做insert数据之前，如果是⾮⽣产环境，请将表的索引和约束去掉，待insert完成后再建索引和约束。

1.

insertintotab1select*fromtab2;

commit;

这是最基础的insert语句，我们把tab2表中的数据insert到tab1表中。根据经验，千万级的数据可在1⼩时内完成。但是该⽅法产⽣的arch会⾮常快，需要关注归档的产⽣量，及时启动

备份软件，避免arch⽬录撑爆。

2.

altertabletab1nologging;

insert/*+append*/intotab1select*fromtab2;

commit;

altertabletab1logging;

该⽅法会使得产⽣arch⼤⼤减少，并且在⼀定程度上提⾼时间，根据经验，千万级的数据可在45分钟内完成。但是请注意，该⽅法适合单进程的串⾏⽅式，如果当有多个进程同时运

⾏时，后发起的进程会有enqueue的等待。注意此⽅法千万不能dataguard上⽤(不过要是在database已经forcelogging那也是不怕的，呵呵)!!

3.

insertintotab1select/*+parallel*/*fromtab2;

commit;

对于select之后的语句是全表扫描的情况，我们可以加parallel的hint来提⾼其并发，这⾥需要注意的是最⼤并发度受到初始化参数parallel_max_servers的限制，并发的进程可以通过

v$px_session查看，或者ps-ef|grepora_p查看。

4.

altersessionenableparalleldml;

insert/*+parallel*/intotab1select*fromtab2;

commit;

与⽅法2相反，并发的insert，尚未⽐较和⽅法2哪个效率更⾼(偶估计是⽅法2快)，有测试过的朋友欢迎补充。

5.

insertintotab1select*fromtab2partition(p1);

insertintotab1select*fromtab2partition(p2);

insertintotab1select*fromtab2partition(p3);

insertintotab1select*fromtab2partition(p4);

对于分区表可以利⽤tab1进⾏多个进程的并发insert，分区越多，可以启动的进程越多。我曾经试过insert2.6亿⾏记录的⼀个表，8个分区，8个进程，如果⽤⽅法2，单个进程完成可

能要40分钟，但是由于是有8个分区8个进程，后发进程有enqueue，所以因此需要的时间为40分钟×8;但是如果⽤⽅法5，虽然单个进程需要110分钟，但是由于能够并发进程执⾏，所以

总共需要的时间就约为110分钟了。

6.

DECLARE

TYPEdtarrayISTABLEOFVARCHAR2(20)INDEXBYBINARY_INTEGER;

v_col1dtarray;

v_col2dtarray;

v_col3dtarray;

BEGIN

SELECTcol1,col2,col3BULKCOLLECT

INTOv_col1,v_col2,v_col3

FROMtab2;

FORALLiIN1..v_

1=v_col1;

END;

⽤批量绑定(bulkbinding)的⽅式。当循环执⾏⼀个绑定变量的sql语句时候，在PL/SQL和SQL引擎(engines)中，会发⽣⼤量的上下⽂切换(contextswitches)。使⽤bulkbinding，能

将数据批量的从plsql引擎传到sql引擎，从⽽减少上下⽂切换过程，提升效率。该⽅法⽐较适合于在线处理，不必停机。

7.

sqlplus-suser/pwd<

setcopycommit2;

setarraysize5000;

copyfromuser/pwd@sid-

touser/pwd@sid-

inserttab1usingselect*fromtab2;

exit

EOF

⽤copy的⽅法进⾏插⼊，注意此处insert没有into关键字。该⽅法的好处是可以设置copycommit和arrarysize来⼀起控制commit的频率，上⾯的⽅法是每10000⾏commit⼀次。