技术
/AlexLin 发表于2007-11-28, 17:39
现在我们有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