forall与bulk collect 节约时间的例子

（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


[e-mail]> alter session set sql_trace=true;

会话已更改。

[e-mail]> set timing on
[e-mail]> 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
[e-mail]> alter session set sql_trace=false;
会话已更改。


[e-mail]> alter session set sql_trace=true;

会话已更改。

已用时间:  00: 00: 00.00
[e-mail]> 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
[e-mail]> 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)