使用bulk collect insert实现大数据快速迁移

在Oracle 数据库中，将一张表的部分字段转换导入到另外一张表的部分字段中，如"insert into t_target(owner,object_name) from t_source;" 。

这也是解决此类数据迁移的的常规操作方法。

如果源表记录数不是很多，操作操作也无妨。

但是若源表记录数一旦超过百万千万，执行时间就长了，占用的回滚段也大。不小心执行失败，又要全部回滚。

有没有更好的方法，加hint 如nologging append, 甚至parallel 。

这些我认为都不是很好的方法，有有一种方法，称之为bulk collect into 。使用它可以减少操作时间。这是基于数据块的插入方式。

 

(miki西游 @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1626906 )

 

目标表的结构如下：

   create table  t_target
  (id         number,
    owner       varchar2(30),
    object_name varchar2(128),
    object_id   number,
    xx          date,
    yy          varchar2(10)
  ) 

 

 

 

源表为dba_objects ，表结构在数据库中可以查到。

 

需要将源表dba_objects 中两个字段object_name,owner 两个字段的值复制到t_target 表中。

declare

  type t_array is table of t_target%rowtype;

  t_data t_array;

  cursor c is

    select null id, owner, object_name, null object_id, null xx, null yy

      from dba_objects;

begin

  open c;

  loop

    fetch c bulk collect

      into t_data limit 100;

    forall i in 1 .. t_data.count

      insert into t_target values t_data (i);

    exit when c%notfound;

  end loop;

  close c;

  commit;

end; 

 

 

Fastest

1. Explicit cursors doing a BULK COLLECT into a local collection (with an appropriate LIMIT) and using FORALL to write back to the database.
2. Implicit cursors doing a BULK COLLECT for you behind the scenes along with single-row writes back to the datbase.
3. Explicit cursors that are not doing a BULK COLLECT and not taking advantage of PL/SQL collections.

 

顺便说一下，这里开发人员多，如果碰到类似要求的，各位可以测试一下这个方法。试试在更大数据量的执行时间和成本会变的如何？

 

 

 

 

评论

2 楼 sunzheng04 2012-12-23  

limit的值与内存使用有关，越大需要运行内存就越多，怎么确定这个值呢？

1 楼 datawarehouse 2012-09-20  

看到过别人使用这个方法