欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

ORACLE批量更新的四种方法对比讲解

程序员文章站 2023-08-26 12:38:57
软件环境 Windows 2000 + ORACLE9i 硬件环境 CPU 1.8G + RAM 512M 现在我们有2张表 如下: T1--大表 10000笔 T1_FK_...

软件环境 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<=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 > 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

方法三.

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

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笔)

方法四.

--由于UPDATE是比较消耗资源的操作,会有redo和undo操作,在这个例子里面我们可以换用下面的方法,创建一张新表,因为采用insert比update快的多,之后你会有一张旧表和一张新表,然后要怎么做就具体情况具体分析了~~~~~

create table T3 as select * from T1 where rownum<1;

alter table T3 nologging;

insert /*+ APPEND */ into T3

select T1.* from T1,T2 where T1.ID=T2.ID;

--耗时0.398s (T1--大表增大到100000笔 T2--小表增大到50000笔)

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

上一篇: 闹钟爆笑笑话

下一篇: 我亲爱的