oracle更新问题
软件环境 Windows 2000 + ORACLE9i?硬件环境 CPU 1.8G + RAM 512M?现在我们有2张表?如下：T1--大表 10000笔?T1_FK_IDT2--小表 5000笔?? T2_PK_IDT1通过表中字段ID与T2的主键ID关联?模拟数据如下：--T2有5000笔数据create table T2asselect 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 T2select rownum id, a.*????? from all_objects a where rownum<=5000;????? --T1有10000笔数据?????????? create table T1asselect 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 T1select rownum sid, T2.*????? from T2;?insert /*+ APPEND */ into T1select rownum sid, T2.*????? from T2;??--更新Subobject_Name字段，之前为nullupdate T2 set T2.Subobject_Name='StevenHuang'?我们希望能把T1的Subobject_Name字段也全部更新成'StevenHuang'，也就是说T1的10000笔数据都会得到更新?方法一写PL/SQL,开cursordeclare ?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;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?方法三.--虚拟一张表来进行操作，在数据量大的情况下效率比方法二高很多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 T3select T1.* from T1,T2 where T1.ID=T2.ID;--耗时0.398s (T1--大表增大到100000笔?T2--小表增大到50000笔)?*以上所有操作都已经将分析执行计划所需的时间排除在外?UPDATE table1 SET userid=( select case??RTRIM(C_RESPCODE )
? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???when 'M'
? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???then 'S'
? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???else 'F'
? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???end as userid??from test2)
? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???where status ='ok'???and ROWNUM<501?declare
n_count number(9) := 0;
begin
? for c in (select rowid ri from TB_DW_BIL_CALL_M ) loop
??? update TB_DW_BIL_CALL_M
??? set id_pri = chjf_seq.nextval
??? where rowid =? c.ri;
??? n_count := n_count + 1;
?
??? if(n_count = 20000) then
??????? commit;
??????? n_count := 0;
??? end if;
? end loop;
? commit;
end;
