Oracle 10g大表批次更新最佳化

Oracle 10g大表批次更新最佳化，其實，700萬的表不算大表，作為測試夠了

一，t表資訊
SQL> alter table t add is_del number(1);
SQL> alter table t modify is_del default 0;

SQL> desc t
Name   Type      Nullable Default Comments
------ --------- -------- ------- --------
ID     NUMBER    Y                       
CODE   NUMBER    Y                       
IS_DEL NUMBER(1) Y        0

SQL> select count(*) from t;
 
  COUNT(*)
----------
  7136976

二，為了比較基準的一致性，先緩衝t資料
update t set t.is_del = 0;

三，這裡共總結了4種方法
SQL> set timing on
--0
SQL> update t set t.is_del = 0;

7136976 rows updated.

Elapsed: 00:08:28.64

--1
SQL> declare
  2    rnt pls_integer := 0;
  3  begin
  4    for idx in (select rowid rid from t) loop
  5      update t set t.is_del = 0 where rowid = idx.rid;
  6      rnt := rnt + 1;
  7      if mod(rnt,2000) = 0 then
  8        commit;
  9      end if;
 10    end loop;
 11    commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:09:41.32
SQL>

--2
SQL> declare
  2    rnt pls_integer := 0;
  3  begin
  4    for idx in (select rowid rid from t) loop
  5      update t set t.is_del = 0 where rowid = idx.rid;
  6      rnt := rnt + 1;
  7      if rnt = 2000 then
  8        rnt := 0;
  9        commit;
 10      end if;
 11    end loop;
 12    commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:09:35.67

--3
SQL> declare
  2    cursor cur_t is select rowid rid from t;
  3    type tab_t is table of urowid index by binary_integer;
  4    l_rid tab_t;
  5  begin
  6    open cur_t;
  7    loop
  8      fetch cur_t bulk collect into l_rid limit 2000;
  9      forall idx in 1 .. l_rid.count
 10        update t set t.is_del = 0 where rowid = l_rid(idx);
 11      commit;
 12      exit when cur_t%notfound;
 13    end loop;
 14    close cur_t;
 15  end;
 16  /

PL/SQL procedure successfully completed.

Elapsed: 00:06:48.84

通過上面的測試結果可以看到，方法3最好，方法0不建議使用，這會使undo快速增長，出現ora-01555錯誤。方法1和方法2在一些書籍上看到過測試，說方法2優於方法1，但我這次測試效果不明顯，以後再進行一些測試。

註：
測試的資料庫配置了閃回特性，db_recovery_file_dest_size=2g，歸檔日誌放在db_recovery_file_dest目錄中。開始時的更新操作，redo增長很快，常常hang住了，alert log報空間不足，所有增加了db_recovery_file_dest_size=4g。還有要注意undo資料表空間的監控。