在很多时候,我们会需要对一个表进行插入大量的数据,并且希望在尽可能短的时间内完成该工作,这里,和大家分享下我平时在做大量数据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一次。
分享到:
相关推荐
ORACLE INSERT INTO SELECT *FROM 分批提交例子
描述了在Oracle中若需要插入大量数据,可以采用的方法。
一个用来把Oracle数据库中的数据转换成INSERT语句的小工具。
Oracle表删除大量数据(千万)后查询变慢问题(原因分析)
这个文件是用oracle的脚本的如的,往oracle中插入的数据量可能会使用
Oracle 分区技术及大数据量操作性能优化.ppt
一个用来把Oracle数据库中的数据转换成INSERT语句的小工具。
一个用来把Oracle数据库中的数据转换成INSERT语句的小工具。
Oracle 表删除大量数据后,即使表中只有几行记录,但用select count(*) from table 来查询发觉都不会马上出来,原因是该表的空间大了
可以把oracle中大量数据导出为txt格式,便于在线下载、存储等后续处理。已经测试过,可以执行。
可以同时连接Mysql和Oracle,直接将数据导入Oracle。也可以保存数据到Dump文件。 功能完善的错误处理机制。记录每一条有异常的数据。并提供工具重复执行。 全面优化数据库操作,让大数据量的处理更少等待。
OracleEBS中文数据字典
oracle千万级别数据简单操作
oracle变化数据捕获,学习和理解如何在ORACLE数据库跟踪数据变化的教程
Oracle EBS中文数据字典.pdf
利用.net 语言编程实现对Oracle数据的批量的导入功能
某些原因,删了一些自己用的函数,此包是我每次导数据老是用工具,如果要导特定几张表的话,如果用工具导入每次都找的够呛,所以花了一点时间写了此过程,如果发现问题或好的建议可以给我留言或者邮箱myrocket_2003@...
表结构的创建比较简单,但是表的数据量太大,一时也想不到怎么把sqlserver表数据复制到oracle中,于是请教公司主管,用存储过程实现可以查询出所有数据的insert脚本,在oracle库中创建好表,直接把sqlserver中的...
oracle导出excel的数据字典sql,根据该sql可以导出数据字典。
非常好用的,速度超快,免费的。请支持原作者,微博名:云计算民工。我只是搬运工。教程请自行去搜。