批量更新資料方法比較測試

xz43發表於2011-01-24
很多情況下，我們會遇到大資料量的批量更新要求，下面，我就來按照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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章