forall与bulk collect 节约时间的例子-优快云博客

本文探讨了在PL/SQL中提高批量更新操作效率的方法，包括使用临时表、FORALL语句及游标的不同实现方式，并通过实证比较了它们之间的性能差异。

（1）看看下面一段pl/sql:

begin
update T2 set  ... where
T2.F1 in ( SELECT T1.F1 FROM T1 WHERE  T1.F2='2009');

update T3 set  ... where
T3.F1 in ( SELECT T1.F1 FROM T1 WHERE  T1.F2='2009');

update T4 set  ... where
T4.F1 in ( SELECT T1.F1 FROM T1 WHERE  T1.F2='2009');
end;

感觉效率不太好，而且写法也有点冗余，把 SELECT T1.F1 FROM T1 WHERE  T1.F2='2009’写了三遍，而且每次更新都要执行这个查询，万一这个查询是个很耗时的操作，那么也太影响效率了；

这个也可以用临时表

（2）可以用forall

TYPE T_F1 IS TABLE OF varchar2(20) TYPE INDEX BY BINARY_INTEGER;

declare
v_f1  T_F1;
begin

SELECT T1.F1 BULK COLLECT into  v_f1  FROM T1 WHERE  T1.F2='2009';

forall i in v_f1  .first .. v_f1  .last
      update T2 set  ... where T2.F1 = v_f1(i);

forall i in v_f1  .first .. v_f1  .last
      update T3 set  ... where T3.F1 = v_f1(i);

forall i in v_f1  .first .. v_f1  .last
      update T4 set  ... where T4.F1 = v_f1(i);

end;

（3）游标，比如：

begin
  for rec in (SELECT T1.F1 FROM T1 WHERE  T1.F2='2009')
  loop
   update T2 set  ... where T2.F1 =rec.f1;
   update T3 set  ... where T2.F1 =rec.f1;
   update T3 set  ... where T2.F1 =rec.f1;
  end loop;

end;
-----

1.你这个forall 的方法和我直接用游标，那个效率更高？
2.循环插入的时候，你用的forall应该比for 效率高，这个我也挺过别人说过；但是我想不都是一条条的处理吗？能有多大区别？

（4）

你这个forall 的方法和我直接用游标，那个效率更高？

下面我来给你实证一下：

SQL> create table tobj as select * from all_objects;

Table created

SQL> create index idx_tobj on tobj(object_id);

Index created

scott@O9I.US.ORACLE.COM> alter session set sql_trace=true;

会话已更改。

scott@O9I.US.ORACLE.COM> set timing on
scott@O9I.US.ORACLE.COM> begin
  2    for c in (SELECT object_id FROM tobj) loop
  3          update tobj set object_name = 'New_' || substrb(object_name, 1, 2
5) where object_id = c.object_id;
  4    end loop;
  5  end;
  6  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 06.02
scott@O9I.US.ORACLE.COM> alter session set sql_trace=false;
会话已更改。

scott@O9I.US.ORACLE.COM> alter session set sql_trace=true;

会话已更改。

已用时间:  00: 00: 00.00
scott@O9I.US.ORACLE.COM> declare
  2    TYPE t_nums IS TABLE OF number;
  3    ids t_nums;
  4  begin
  5    SELECT object_id BULK COLLECT into  ids  FROM tobj;
  6    forall i in ids.first .. ids.last
  7          update tobj set object_name = 'New_' || substrb(object_name, 1, 2
5) where object_id = ids(i);
  8  end;
  9  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 03.08
scott@O9I.US.ORACLE.COM> alter session set sql_trace=false;

会话已更改。

下面看tkprof结果中的关键部分：

第一个方法(cursor)：

UPDATE tobj set object_name = 'New_' || substrb(object_name, 1, 25)
where
object_id = :b1

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       1    0.00    0.00       0       0       0          0
Execute  38102    1.98    2.67       0    76569    40221    38102
Fetch       0    0.00    0.00       0       0       0          0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total 38103    1.98    2.67       0    76569    40221    38102

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59    (recursive depth: 1)

Rows    Row Source Operation
-------  ---------------------------------------------------
   0  UPDATE  (cr=76581 r=0 w=0 time=1907615 us)
  38102 INDEX RANGE SCAN IDX_TOBJ (cr=76287 r=0 w=0 time=466460 us)(object id 75110)

第二个方法(for all):

UPDATE tobj set object_name = 'New_' || substrb(object_name, 1, 25)
where
object_id = :b1

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       1    0.00    0.00       0       0       0          0
Execute    1    1.53    3.34       0    38190    39786    38102
Fetch       0    0.00    0.00       0       0       0          0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       2    1.53    3.34       0    38190    39786    38102

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59    (recursive depth: 1)

Rows    Row Source Operation
-------  ---------------------------------------------------
   0  UPDATE  (cr=38199 r=0 w=0 time=3109225 us)
  38102 INDEX RANGE SCAN IDX_TOBJ (cr=38187 r=0 w=0 time=382925 us)(object id 75110)