在大型的數(shù)據(jù)庫(kù)應(yīng)用中，我們經(jīng)常會(huì)有針對(duì)表與表之間的關(guān)鍵建進(jìn)行字段更新，那么在這個(gè)時(shí)候，我們就不能寫簡(jiǎn)單的update來(lái)實(shí)現(xiàn)更新操作，而要針對(duì)具體的數(shù)據(jù)量來(lái)進(jìn)行批量的update,下面幾個(gè)例子是常用的SQL,將其做個(gè)對(duì)比，歡迎大家提出更好，更高效的SQL實(shí)現(xiàn)。
數(shù)據(jù)庫(kù)：Oracle 9i 測(cè)試工具：PL/SQL
定義2張測(cè)試表：T1,T2
T1--大表 10000條 T1_FK_ID
T2--小表 5000條 T2_PK_ID
T1通過(guò)表中字段ID與T2的主鍵ID關(guān)聯(lián)
模擬數(shù)據(jù)如下：
--T2有5000條記錄
create table T2 as select rownum id, a.* from all_objects a where 1=0;
//T2表的字段和all_objects表字段類型以及默認(rèn)值一致，但索引初始化了，需要重新設(shè)置
--創(chuàng)建主鍵ID,向T2表copy數(shù)據(jù)
alter table T2 add constraint T2_PK_ID primary key (ID);
insert 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;
-- 創(chuàng)建外鍵ID,向T1表copy數(shù)據(jù)
alter table T1 add constraint T1_FK_ID foreign key (ID) references t2 (ID);
insert into T1 select rownum sid, T2.* from T2;
insert into T1 select rownum sid, T2.* from T2;
--更新Subobject_Name字段，初始為NULL
update T2 set T2.Subobject_Name='StevenHuang'
需求：我們希望能把T1表的Subobject_Name字段也全部更新成'StevenHuang'，也就是說(shuō)T1的10000條記錄都會(huì)得到更新，以下SQL語(yǔ)句均在PL/SQL命令窗口測(cè)試。
方法一:
寫PL/SQL,開(kāi)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;---耗時(shí)39.716s
顯然這是最傳統(tǒng)的方法，如果數(shù)據(jù)量巨大的話（4000萬(wàn)記），還會(huì)報(bào)”snapshot too old”錯(cuò)誤退出,PL/SQL工具會(huì)掛掉
方法二:
用loop循環(huán)，分批update
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;
--耗時(shí)0.656s，這里一共循環(huán)了10次，如果數(shù)據(jù)量巨大的話，雖然能夠完成任務(wù)，但是速度還是不能令人滿意。(例如我們將T1--大表增大到100000記錄 T2--小表增大到50000記錄,將耗時(shí)10.139s)
方法三:
--虛擬一張表來(lái)進(jìn)行操作，在數(shù)據(jù)量大的情況下效率比方法二高很多.
注：此語(yǔ)句下T1,T2表中必須有相應(yīng)的主外建關(guān)聯(lián)，否則sql編譯不能通過(guò).
update (select T1.Subobject_Name A1,T2.Subobject_Name B1 from T1,T2 where T1.ID=T2.ID) set A1=B1;
--耗時(shí)3.234s (T1--大表增大到100000記錄 T2--小表增大到50000記錄)
*以上所有操作都已經(jīng)將分析執(zhí)行計(jì)劃所需的時(shí)間排除在外