转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<=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笔)
*以上所有操作都已经将分析执行计划所需的时间排除在外 (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|