ORACLE批次更新四種方法比較

  軟體環境 Windows 2000 + ORACLE9i  硬體環境 CPU 1.8G + RAM 512M   現在我們有2張表 如下： T1--大表 10000筆 T1_FK_ID T2--小表 5000筆    T2_PK_ID T1通過表中欄位ID與T2的主鍵ID關聯   類比資料如下： --T2 有 5000 筆資料 create table T2 as select rownum id, a.*  from all_objects a  where 1=0;   -- Create/Recreate primary, unique and foreign key constraints alter table T2  add constraint T2_PK_ID primary key (ID);   insert /*+ APPEND */ into T2 select rownum id, a.*       from all_objects a where rownum<=5000;       --T1 有 10000 筆資料            create table T1 as select rownum sid, T2.*  from T2  where 1=0;   -- Create/Recreate primary, unique and foreign key constraints alter table T1  add constraint T1_FK_ID foreign key (ID)  references t2 (ID);   insert /*+ APPEND */ into T1 select rownum sid, T2.*       from T2;   insert /*+ APPEND */ into T1 select rownum sid, T2.*       from T2;     -- 更新 Subobject_Name 欄位，之前為 null update T2 set T2.Subobject_Name='StevenHuang'   我們希望能把 T1 的 Subobject_Name 欄位也全部更新成 'StevenHuang' ，也就是說 T1 的 10000 筆資料都會得到更新   方法一 寫 PL/SQL, 開 cursor declare  l_varID varchar2(20);  l_varSubName varchar2(30);  cursor mycur is select T2.Id,T2.Subobject_Name from T2;   begin  open mycur;   loop       fetch mycur into l_varID,l_varSubName;       exit when mycur %notfound;       update T1 set T1.Subobject_Name = l_varSubName where T1.ID = l_varID;  end loop;  close mycur; end; --- 耗時 39.716s 顯然這是最傳統的方法，如果資料量巨大的話（ 4000 萬筆），還會報 ”snapshot too old” 錯誤退出   方法二. 用 loop 迴圈，分批操作 declare  i number;  j number; begin i := 1; j := 0; select count(*) into j from T1; loop exit when i > j; update T1 set T1.Subobject_Name = (select T2.Subobject_Name from T2 where T1.ID = T2.ID) where T1.ID >= i and T1.ID <= (i + 1000); i := i + 1000; end loop; end; -- 耗時 0.656s ，這裡一共迴圈了 10 次，如果資料量巨大的話，雖然能夠完成任務，但是速度還是不能令人滿意。 ( 例如我們將 T1-- 大表 增大到 100000 筆  T2-- 小表 增大到 50000 筆 )  耗時 10.139s   方法三. -- 虛擬一張表來進行操作，在資料量大的情況下效率比方法二高很多 update (select T1.Subobject_Name a1,T2.Subobject_Name b1 from T1,T2 where T1.ID=T2.ID) set a1=b1; -- 耗時 3.234s (T1-- 大表 增大到 100000 筆  T2-- 小表 增大到 50000 筆 )   方法四. -- 由於 UPDATE 是比較消耗資源的操作，會有 redo 和 undo 操作，在這個例子裡面我們可以換用下面的方法，建立一張新表，因為採用 insert 比 update 快的多，之後你會有一張舊錶和一張新表，然後要怎麼做就具體情況具體分析了 ~~~~~ create table T3 as select * from T1 where rownum<1; alter table T3 nologging; insert /*+ APPEND */ into T3 select T1.* from T1,T2 where T1.ID=T2.ID; -- 耗時 0.398s (T1-- 大表 增大到 100000 筆  T2-- 小表 增大到 50000 筆 )     * 以上所有操作都已經將分析執行計畫所需的時間排除在外