批量更新数据方法比较测试

很多情况下，我们会遇到大数据量的批量更新要求，下面，我就来按照rowid的方式（这应该是最快的方法），来比较几种处理方式之间的性能差异，由于机器性能（PC机）及时间原因，这里只准备了460W条数据，比较各种方案循环处理的时间，比较结果。

SQL> select count(1) from testA;

  COUNT(1)
----------
   4643600
SQL> set timing on

第一种情况，无序rowid的游标循环更新：

SQL> declare
  cursor upd_cursor is
    select rowid as row_id from testA;
  rowd      varchar(30);
  v_counter number;
begin
  open upd_cursor;
  loop
    fetch upd_cursor
      into rowd;
    exit when upd_cursor%notfound;
    execute immediate 'update testA set flag=:f where rowid=:r'
      using 1, rowd;
    if (v_counter >= 1000) then
      commit;
      v_counter := 0;
    end if;
  end loop;
  close upd_cursor;
  commit;
end;
/

PL/SQL 过程已成功完成。

已用时间:  00: 05: 46.78

 

第二种情况，有序rowid的游标循环更新：

SQL> declare
  cursor upd_cursor is
    select rowid as row_id from testA order by rowid;
  rowd      varchar(30);
  v_counter number;
begin
  open upd_cursor;
  loop
    fetch upd_cursor
      into rowd;
    exit when upd_cursor%notfound;
    execute immediate 'update testA set flag=:f where rowid=:r'
      using 1, rowd;
    if (v_counter >= 1000) then
      commit;
      v_counter := 0;
    end if;
  end loop;
  close upd_cursor;
  commit;
end;
/

PL/SQL 过程已成功完成。

已用时间:  00: 05: 30.03

 

第三种情况，无序rowid的bulk collect更新：

SQL> declare
  type objecttab is table of varchar2(20);
  rowd objecttab;
  cursor upd_cursor is
    select rowid as row_id from testA;
  v_counter number;
begin
  open upd_cursor;
  fetch upd_cursor bulk collect
    into rowd;
  close upd_cursor;
  for i in rowd.first .. rowd.last loop
    execute immediate 'update testA set flag=:f where rowid=:r'
      using 2, rowd(i);
    if (v_counter >= 1000) then
      commit;
      v_counter := 0;
    end if;
  end loop;
  commit;
end;
/

PL/SQL 过程已成功完成。

已用时间:  00: 04: 29.80

 

第四种情况，有序rowid的bulk collect更新：

SQL> declare
  type objecttab is table of varchar2(20);
  rowd objecttab;
  cursor upd_cursor is
    select rowid as row_id from testA order by rowid;
  v_counter number;
begin
  open upd_cursor;
  fetch upd_cursor bulk collect
    into rowd;
  close upd_cursor;
  for i in rowd.first .. rowd.last loop
    execute immediate 'update testA set flag=:f where rowid=:r'
      using 2, rowd(i);
    if (v_counter >= 1000) then
      commit;
      v_counter := 0;
    end if;
  end loop;
  commit;
end;
/

PL/SQL 过程已成功完成。

已用时间:  00: 04: 41.76

最后，再来看看forall，对于这种循环，建议更新的数据量不要太大，效果还是可以的，对于这里的数据量已经不合适。

SQL> declare
  type objecttab is table of varchar2(20);
  rowd objecttab;
  cursor upd_cursor is
    select rowid as row_id from testA order by rowid;
  v_counter number;
begin
  open upd_cursor;
  fetch upd_cursor bulk collect
    into rowd;
  close upd_cursor;
  forall i in rowd.first .. rowd.last execute immediate
                            'update testA set flag=:f where rowid=:r' using 1,
                            rowd(i)
    ;
  commit;
end;
/

手动给停止了，这种数据级一次性提交太不合适了。

 

总结：对于这个数据量级的更新，是否按rowid的顺序去更新，对性能的影响不是很大，可能和我数据的存储有关（测试数据是我一次性插入的，可能分布本身就很连续了）。若是那种分布不连续，而且数据量很大的话，按rowid顺序会去读取更新快些。还有，貌似bulk collect适当使用，效率会比游标的效率更高。

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9399028/viewspace-684553/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9399028/viewspace-684553/