posts - 179,comments - 176,trackbacks - 0

在大型的数据库应用中,我们经常会有针对表与表之间的关键建进行字段更新,那么在这个时候,我们就不能写简单的update来实现更新操作,而要针对具体的数据量来进行批量的update,下面几个例子是常用的SQL,将其做个对比,欢迎大家提出更好,更高效的SQL实现。


数据库:Oracle 9i  测试工具:PL/SQL

定义2张测试表:T1,T2
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;
//T2表的字段和all_objects表字段类型以及默认值一致,但索引初始化了,需要重新设置

--创建主键ID,向T2表copy数据
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;

-- 创建外键ID,向T1表copy数据
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条记录都会得到更新,以下SQL语句均在PL/SQL命令窗口测试。

方法一:
写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”错误退出,PL/SQL工具会挂掉

方法二:
用loop循环,分批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;

--耗时0.656s,这里一共循环了10次,如果数据量巨大的话,虽然能够完成任务,但是速度还是不能令人满意。(例如我们将T1--大表增大到100000记录 T2--小表增大到50000记录,将耗时10.139s)

方法三:
--虚拟一张表来进行操作,在数据量大的情况下效率比方法二高很多.
   注:此语句下T1,T2表中必须有相应的主外建关联,否则sql编译不能通过.

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记录)
*以上所有操作都已经将分析执行计划所需的时间排除在外

 
转:http://alex.bloghome.cn/posts/144915.html  
注:以上传载的文章中的SQL经过我测试,其结果和文章中的执行结果的时间是吻合的,有兴趣的朋友可以自己测试一下。

posted on 2007-12-28 20:11 cheng 阅读(8840) 评论(0)  编辑  收藏 所属分类: Oracle

只有注册用户登录后才能发表评论。


网站导航: