Oracle insert大数据量经验之谈

小灰灰 2022-05-22 03:48 304阅读 0赞

在很多时候，我们会需要对一个表进行插入大量的数据，并且希望在尽可能短的时间内完成该工作，这里，和大家分享下我平时在做大量数据insert的一些经验。

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

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

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

对于select之后的语句是全表扫描的情况，我们可以加parallel的hint来提高其并发，这里需要注意的是最大并发度受到初始化参数parallel\_max\_servers的限制，并发的进程可以通过v$px\_session查看，或者ps -ef |grep ora\_p查看。

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

对于分区表可以利用tab1进行多个进程的并发insert，分区越多，可以启动的进程越多。我曾经试过insert 2.6亿行记录的一个表，8个分区，8个进程，如果用方法2，单个进程完成可能要40分钟，但是由于是有8个分区8个进程，后发进程有enqueue，所以因此需要的时间为40分钟×8;但是如果用方法5，虽然单个进程需要110分钟，但是由于能够并发进程执行，所以总共需要的时间就约为110分钟了。

<table style="margin:0px;padding:0px;border-spacing:0px;border:1px solid #C0C0C0;color:rgb(0,0,0);font-family:Verdana, Arial, Helvetica, sans-serif;font-size:15px;background-color:rgb(255,255,255);width:1019px;height:179px;"> 
 <tbody style="margin:0px;padding:0px;"> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;"><br style="margin:0px;padding:0px;">　　DECLARE&nbsp;<br style="margin:0px;padding:0px;">　　TYPE&nbsp;dtarray&nbsp;IS&nbsp;TABLE&nbsp;OF&nbsp;VARCHAR2(20)&nbsp;INDEX&nbsp;BY&nbsp;BINARY_INTEGER;&nbsp;<br style="margin:0px;padding:0px;">　　v_col1&nbsp;dtarray;&nbsp;<br style="margin:0px;padding:0px;">　　v_col2&nbsp;dtarray;&nbsp;<br style="margin:0px;padding:0px;">　　v_col3&nbsp;dtarray;&nbsp;<br style="margin:0px;padding:0px;">　　BEGIN&nbsp;<br style="margin:0px;padding:0px;">　　SELECT&nbsp;col1,&nbsp;col2,&nbsp;col3&nbsp;BULK&nbsp;COLLECT&nbsp;<br style="margin:0px;padding:0px;">　　INTO&nbsp;v_col1,&nbsp;v_col2,&nbsp;v_col3&nbsp;<br style="margin:0px;padding:0px;">　　FROM&nbsp;tab2;&nbsp;<br style="margin:0px;padding:0px;">　　FORALL&nbsp;i&nbsp;IN&nbsp;1&nbsp;..&nbsp;v_col1.COUNT&nbsp;<br style="margin:0px;padding:0px;">　　insert&nbsp;into&nbsp;tab1&nbsp;WHERE&nbsp;tab1.col1&nbsp;=&nbsp;v_col1;&nbsp;<br style="margin:0px;padding:0px;">　　END;</td> 
  </tr> 
 </tbody> 
</table>

用批量绑定(bulk binding)的方式。当循环执行一个绑定变量的sql语句时候，在PL/SQL 和SQL引擎(engines)中，会发生大量的上下文切换(context switches)。使用bulk binding，能将数据批量的从plsql引擎传到sql引擎，从而减少上下文切换过程，提升效率。该方法比较适合于在线处理，不必停机。

<table style="margin:0px;padding:0px;border-spacing:0px;border:1px solid #C0C0C0;color:rgb(0,0,0);font-family:Verdana, Arial, Helvetica, sans-serif;font-size:15px;background-color:rgb(255,255,255);width:1036px;height:126px;"> 
 <tbody style="margin:0px;padding:0px;"> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;"><br style="margin:0px;padding:0px;">　　sqlplus&nbsp;-s&nbsp;user/pwd&lt;&nbsp;runlog.txt&nbsp;<br style="margin:0px;padding:0px;">　　set&nbsp;copycommit&nbsp;2;&nbsp;<br style="margin:0px;padding:0px;">　　set&nbsp;arraysize&nbsp;5000;&nbsp;<br style="margin:0px;padding:0px;">　　copy&nbsp;from&nbsp;user/pwd@sid&nbsp;-&nbsp;<br style="margin:0px;padding:0px;">　　to&nbsp;user/pwd@sid&nbsp;-&nbsp;<br style="margin:0px;padding:0px;">　　insert&nbsp;tab1&nbsp;using&nbsp;select&nbsp;*&nbsp;from&nbsp;tab2;&nbsp;<br style="margin:0px;padding:0px;">　　exit&nbsp;<br style="margin:0px;padding:0px;">　　EOF</td> 
  </tr> 
 </tbody> 
</table>

用copy的方法进行插入，注意此处insert没有into关键字。该方法的好处是可以设置copycommit和arrarysize来一起控制commit的频率，上面的方法是每10000行commit一次。

希望大家多多在评论区留言，一起探讨！