oracle事务_笔记6：在循环中提交

在循环中提交

到目前为止，最好的办法是按业务过程的要求以适当的频度提交，并且相应地设置undo段的大小。

性能影响

如果频繁地提交，通常并不会更快。一般地，在一个SQL语句中完成工作几乎总是否更快一些。

可以通过一个小例子来说明，假设我们有一个表T，其中有大量的行，而且我们希望为该表中的每一行更新一个列值。

scott@ORCL>drop table t;

Table dropped.

scott@ORCL>create table t as select * from all_objects;

Table created.

scott@ORCL>exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

scott@ORCL>variable n number

这样一来，更新时，只需简单地在一条UPDATE语句中完成，

scott@ORCL>exec :n :=dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

scott@ORCL>update t set object_name=lower(object_name);

56300 rows updated.

scott@ORCL>exec dbms_output.put_line((dbms_utility.get_cpu_time-:n) || 'cpu hsecs...');

161cpu hsecs...

PL/SQL procedure successfully completed.

不过大多数人更喜欢像下面这样，即采用慢速处理/逐行处理提交N个记录：

scott@ORCL>exec :n :=dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

scott@ORCL>begin

2 for x in(select rowid rid,object_name,rownum r

3 from t)

4 loop

5 update t

6 set object_name=lower(x.object_name)

7 where rowid=x.rid;

8 if (mod(x.r,100)=0) then

9 commit;

10 end if;

11 end loop;

12 commit;

13 end;

14 /

PL/SQL procedure successfully completed.

scott@ORCL>exec dbms_output.put_line((dbms_utility.get_cpu_time-:n) || 'cpu hsesc...');

283cpu hsesc...

PL/SQL procedure successfully completed.

对于这个小例子，倘若在循环中频繁地提交，就会慢上好几倍。如果能在一条SQL语句中完成，就要尽量这么做，因为这样几乎总是更快。

scott@ORCL>declare

2 type ridArray is table of rowid;

3 type vcArray is table of t.object_name%type;

4 l_rids ridArray;

5 l_names vcArray;

6 cursor c is select rowid,object_name from t;

7 begin

8 open c;

9 loop

10 fetch c bulk collect into l_rids,l_names LIMIT 100;

11 forall i in 1..l_rids.count

12 update t

13 set object_name=lower(l_names(i))

14 where rowid=l_rids(i);

15 commit;

16 exit when c%notfound;

17 end loop;

18 close c;

19 end;

20 /

PL/SQL procedure successfully completed.

scott@ORCL>exec dbms_output.put_line((dbms_utility.get_cpu_time-:n) || 'cpu hsecs...');

144cpu hsecs...

PL/SQL procedure successfully completed.

这确实要快一些，但是本来还可以更快的。不仅如此，你还应该注意到这段代码变得越来越复杂。

另外，前面的过程代码还没有完。它没有处理“当我们失败”的情况。如果这个代码完成了一半，然后系统失败了，会发生什么情况?如果用提交重新启动这个过程代码?你还必须增加一些代码，从而知道从哪里继续处理。

如果要针对一个行子集(有一个WHERE子句)执行先前的UPDATE语句，而其他用户正在修改这个UPDATE在WHERE子句中使用的列，就可能需要使用一系列较小的事务而不是一个大事务，或者更适合在执行大量更新之前先锁定表。这样做的目标是减少出现重启动的机会。如果要UPDATE表中的大量行，这会导致我们LOCK TABLE命令。

一次性的大量更新或清除旧数据通常不会在活动高发期间完成。实际上，数据的清除根本不应受此影响，因为我们一般会使用某个日期字段来定位要清除的信息，而其他应用不会修改这个日期数据。

本文来源 我爱IT技术网 http://www.52ij.com/jishu/5241.html 转载请保留链接。