ORACLE批量更新三种方法比较.docVIP

ORACLE批量更新三种方法比较在大型的数据库应用中，我们经常会有针对表与表之间的关键建进行字段更新，那么在这个时候，我们就不能写简单的update来实现更新操作，而要针对具体的数据量来进行批量的update,下面几个例子是常用的SQL,将其做个对比，欢迎大家提出更好，更高效的SQL实现。 数据库：Oracle 9i 测试工具：PL/SQL 定义2张测试表：T1,T2T1--大表 10000条 T1_FK_ID T2--小表 5000条 T2_PK_IDT1通过表中字段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字段，初始为NULLupdate T2 set T2.Subobject_Name=StevenHuang 需求：我们希望能把T1表的Subobject_Name字段也全部更新成StevenHuang，也就是说T1的10000条记录都会得到更新，以下SQL语句均在PL/SQL命令窗口测试。方法一:写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; loopfetch 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”错误退出,PL/SQL工具会挂掉方法二:用loop循环，分批update declare i number;j number;begini := 1;j := 0;select count(*) into j from T1;loopexit 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) 方法三:--虚拟一张表来进行操作，在数据量大的情况下效率比方法二高很多. 注：此语句下T1,T2表中必须有相应的主外建关联，否则sql编译不能通过. update (select T1.Subobject_Name A1,T2.Subobject_Name B1 from T1,T2 where T1.ID=T2.ID) set