进行“批收集”及其时间选择的几中方法

这篇技巧性的文章介绍了进行“批收集”及其时间选择的几中方法。这些方法已经在Oracle 9.2.0.3.0上通过测试。下面是我要用到的表。它包含一些长字符串列。
create table bulktest (w number, x varchar2(100), y varchar2(100), z varchar2(100));

产生数据库表：
declare
  type typ_bulktest is table of bulktest%rowtype;
  arr_bulktest typ_bulktest := typ_bulktest();
begin
  arr_bulktest.extend(100000);
  for i in 1 .. 100000 loop
    arr_bulktest(i).w := i;
    arr_bulktest(i).x := rpad(i,100,'x');
    arr_bulktest(i).y := rpad(i,100,'y');
    arr_bulktest(i).z := rpad(i,100,'z');
  end loop;
  forall i in arr_bulktest.first .. arr_bulktest.last
    insert into bulktest
    values arr_bulktest(i);
end;
/

测试1：基于table%rowtype，在数组中放入隐含游标
declare
  type typ_bulktest is table of bulktest%rowtype;
  arr_bulktest typ_bulktest := typ_bulktest();
begin
  select *
  bulk collect into arr_bulktest
  from bulktest;
  dbms_output.put_line('bulk collected: ' || arr_bulktest.count);
end;
/
bulk collected: 100000
Elapsed: 00:00:01.13
Elapsed: 00:00:01.10
Elapsed: 00:00:01.08
Elapsed: 00:00:01.09
Elapsed: 00:00:01.09

测试2：往标量类型的并行数组中放入隐含游标
declare
  type typ_bulktest_w is table of bulktest.w%type;
  type typ_bulktest_x is table of bulktest.x%type;
  type typ_bulktest_y is table of bulktest.y%type;
  type typ_bulktest_z is table of bulktest.z%type;
  arr_bulktest_w typ_bulktest_w := typ_bulktest_w();
  arr_bulktest_x typ_bulktest_x := typ_bulktest_x();
  arr_bulktest_y typ_bulktest_y := typ_bulktest_y();
  arr_bulktest_z typ_bulktest_z := typ_bulktest_z();
begin
  select *
  bulk collect into
  arr_bulktest_w, arr_bulktest_x, arr_bulktest_y, arr_bulktest_z
  from bulktest;
  dbms_output.put_line('bulk collected: ' || arr_bulktest_w.count);
end;
/
bulk collected: 100000
Elapsed: 00:00:01.14
Elapsed: 00:00:01.12
Elapsed: 00:00:01.13
Elapsed: 00:00:01.13
Elapsed: 00:00:01.12

测试3：基于table%rowtype，往数组中放入“动态参考游标”。

declare
  v_ref sys_refcursor;
  type typ_bulktest is table of bulktest%rowtype;
  arr_bulktest typ_bulktest := typ_bulktest();
begin
  open v_ref for 'select * from bulktest';
  fetch v_ref bulk collect into arr_bulktest;
  dbms_output.put_line('bulk collected: ' || arr_bulktest.count);
end;
/
bulk collected: 100000
Elapsed: 00:00:01.12
Elapsed: 00:00:01.10
Elapsed: 00:00:01.10
Elapsed: 00:00:01.10
Elapsed: 00:00:01.10

测试4：往标量类型的并行数组中放入“动态参考游标”。
declare
  v_ref sys_refcursor;
  type typ_bulktest_w is table of bulktest.w%type;
  type typ_bulktest_x is table of bulktest.x%type;
  type typ_bulktest_y is table of bulktest.y%type;
  type typ_bulktest_z is table of bulktest.z%type;
  arr_bulktest_w typ_bulktest_w := typ_bulktest_w();
  arr_bulktest_x typ_bulktest_x := typ_bulktest_x();
  arr_bulktest_y typ_bulktest_y := typ_bulktest_y();
  arr_bulktest_z typ_bulktest_z := typ_bulktest_z();
begin
  open v_ref for 'select * from bulktest';
  fetch v_ref bulk collect into
  arr_bulktest_w, arr_bulktest_x, arr_bulktest_y, arr_bulktest_z;
  dbms_output.put_line('bulk collected: ' || arr_bulktest_w.count);
end;
/
bulk collected: 100000
Elapsed: 00:00:01.14
Elapsed: 00:00:01.12
Elapsed: 00:00:01.12
Elapsed: 00:00:01.12
Elapsed: 00:00:01.13

测试5：我的个人偏好：基于cursor%rowtype，往数组中放入明显的游标。
明显的游标给你查询上的弹性，并且，假如SELECT子句改变的话，你无需改写“批收集”。
declare
  cursor cur_bulktest is
  select * from bulktest;
  type typ_bulktest is table of cur_bulktest%rowtype;
  arr_bulktest typ_bulktest := typ_bulktest();
begin
  open cur_bulktest;
  fetch cur_bulktest
  bulk collect into arr_bulktest;
  dbms_output.put_line('bulk collected: ' || arr_bulktest.count);
end;
/
bulk collected: 100000
Elapsed: 00:00:01.41
Elapsed: 00:00:01.09
Elapsed: 00:00:01.10
Elapsed: 00:00:01.08
Elapsed: 00:00:01.09

分析：在Oracle 9.2中，不再需要使用并行标量游标。升级到Oracle 9.2.0.3.0是非常好的（我认为Oracle 9.2.0.4.0是最新的补丁修订版本），这样会给系统增加很多特色。