---
title: "ORACLE批量更新四种方法比较"
author: "winzenghua"
date: "2009-09-08T18:33:00Z"
tags: []
slug: "oracle"
draft: false
lastmod: "2009-09-08T18:33:00Z"
id: 987854
url: "https://java4gis.iteye.com/blog/987854"
---

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&lt;=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 &gt; j;  
update T1 set T1.Subobject\_Name = (select T2.Subobject\_Name from T2 where T1.ID = T2.ID)  
where T1.ID &gt;= i and T1.ID &lt;= (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