oracle 分批提交插入900万数据的方法对比

1.使用循环追条插入方法

15:54:40 SQL> declare
           2    v_cnt number := 0;
           3  begin
           4    for i in 1000001 .. 9999999 loop
           5      --定义从 1000001开始，9999999结束
           6      insert into tmp_wrh_1 (random_rn, used) values (i, null);
           7      v_cnt := v_cnt + 1;
           8      if mod(v_cnt, 1000) = 0 then
           9        --每插入1000条记录插入一次
          10        commit;
          11      end if;
          12    end loop;
          13    commit;
          14  
          15  end;
          16  /
PL/SQL procedure successfully completed

Executed in 779.209 seconds

--------------------------------------------------------------------------------------------------------------------------------

2.先生成记录插入一张临时表，然后把临时表以dmp的格式将数据导入到相同的schema下,然后再从临时表插入到正式表

17:12:47 SQL> declare
           2    v_cnt number := 0;
           3  begin
           4    for i in (select * from tmp_wrh_1) loop
           5      --定义从 1000001开始，9999999结束
           6      insert into tmp_wrh_2 (random_rn, used) values (i.random_rn, i.used);
           7      v_cnt := v_cnt + 1;
           8      if mod(v_cnt, 1000) = 0 then
           9        --每插入1000条记录插入一次
          10        commit;
          11      end if;
          12    end loop;
          13    commit;
          14  
          15  end;
          16  /
          
PL/SQL procedure successfully completed

Executed in 835.572 seconds

----------------------------------------------------------------------------------------------------------------------

3.使用 bulk collect从临时表取数据集合、   for 逐条插入方法导入 

16:22:04 SQL> declare
           2    v_cnt number := 0;
           3    type type_record is table of T_FSPF_OMMS_N.tmp_wrh_1%rowtype index by binary_integer;
           4    v_type_record type_record;
           5  begin
           6  
           7    select t.random_rn,t.used bulk collect into v_type_record from T_FSPF_OMMS_N.tmp_wrh_1 t ;
           8  
           9    for i in 1 .. v_type_record.count loop
          10      --定义从 1000001开始，9999999结束
          11      insert into T_FSPF_OMMS_N.tmp_wrh_2 (random_rn, used) values (v_type_record(i).random_rn, v_type_record(i).used);
          12      v_cnt := v_cnt + 1;
          13      if mod(v_cnt, 1000) = 0 then
          14        --每插入1000条记录插入一次
          15        commit;
          16      end if;
          17    end loop;
          18    commit;
          19  
          20  end;
          21  /
PL/SQL procedure successfully completed

Executed in 1583.785 seconds

-------------------------------------------------------------------------------------------------------------------------------

4.使用 游标，然后使用bulk collect 从临时表读取数据集合，使用forall 批量插入

17:02:32 SQL> declare
           2    v_cnt number := 0;
           3    type type_record is table of T_FSPF_OMMS_N.tmp_wrh_1%rowtype index by binary_integer;
           4    v_type_record type_record;
           5  
           6    cursor cur_type_record is select * from T_FSPF_OMMS_N.tmp_wrh_1;
           7  begin
           8  
           9     open cur_type_record;
          10     loop
          11       fetch cur_type_record bulk collect into v_type_record limit 1000;
          12       forall i in 1 .. v_type_record.count
          13         insert into T_FSPF_OMMS_N.tmp_wrh_2 (random_rn, used) values (v_type_record(i).random_rn, v_type_record(i).used);
          14         v_cnt := v_cnt + 1;
          15      if mod(v_cnt, 1000) = 0 then
          16        --每插入1000条记录插入一次
          17        commit;
          18      end if;
          19      exit when cur_type_record%notfound;
          20       end loop;
          21    commit;
          22  
          23  end;
          24  /
PL/SQL procedure successfully completed

Executed in 35.817 seconds

总结：

   使用方法4效率最高。