Oracle批量更新四种方法比较

软件环境 Windows 2000 + Oracle9i 硬件环境 CPU 1.8G + RAM 512M 现在我们有2张表 如下：T1--大表 10000笔 T1_FK_ID T2--小表 5

软件环境 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     
--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 := 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 rownumalter table t3 nologging;
insert /*+ append */ into t3
select t1.* from t1,t2 where t1.id=t2.id;
--耗时0.398s (t1--大表增大到100000笔 t2--小表增大到50000笔)
 
*以上所有操作都已经将分析执行计划所需的时间排除在外

Hugging Face

Hugging Face AI开源社区

135

查看详情