java oracle 批量更新_ORACLE批量更新三种方法比较

在大型的数据库应用中，我们经常会有针对表与表之间的关键建进行字段更新，那么在这个时候，我们就不能写简单的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_varIDvarchar2(20);

 l_varSubNamevarchar2(30);

cursormycurisselectT2.Id,T2.Subobject_NamefromT2;

beginopenmycur; 

 loop

fetchmycurintol_varID,l_varSubName;

exitwhenmycur%notfound;

updateT1setT1.Subobject_Name=l_varSubNamewhereT1.ID=l_varID;

endloop;

closemycur;

end;

---耗时39.716s

显然这是最传统的方法，如果数据量巨大的话(4000万记)，还会报”snapshot too old”错误退出,PL/SQL工具会挂掉

方法二:

用loop循环，分批update

declare  inumber;

  jnumber;

begin  i :=1;

  j :=0;

selectcount(*)intojfromT1;

  loop

exitwheni>j;

updateT1setT1.Subobject_Name=(selectT2.Subobject_NamefromT2whereT1.ID=T2.ID)whereT1.ID>=iandT1.ID<

(i+1000);

    i :=i+1000;

endloop;

end;

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

方法三:

--虚拟一张表来进行操作，在数据量大的情况下效率比方法二高很多.

注：此语句下T1,T2表中必须有相应的主外建关联，否则sql编译不能通过.

update(selectT1.Subobject_Name A1,T2.Subobject_Name B1fromT1,T2whereT1.ID=T2.ID)setA1=B1; 

--耗时3.234s (T1--大表增大到100000记录 T2--小表增大到50000记录)

*以上所有操作都已经将分析执行计划所需的时间排除在外

转：http://alex.bloghome.cn/posts/144915.html

注：以上传载的文章中的SQL经过我测试，其结果和文章中的执行结果的时间是吻合的，有兴趣的朋友可以自己测试一下。

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