Several approaches to bulk collection

This tip presents several approaches to bulk collecting and timings.

This tip presents several approaches to bulk collecting and timings. Tests were run on Oracle 9.2.0.3.0. This following is the table I will use. It has a few long string columns.

create table bulktest (w number, x varchar2(100), y varchar2(100), z varchar2(100));

Populate the table:

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;
/

Test 1: Implicit cursor into array based on 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

Test 2: Implicit cursor into parallel arrays of scalar types.

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

Test 3: Dynamic ref cursor into array based on 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

Test 4: Dynamic ref cursor into parallel arrays of scalar types.

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

Test 5: My personal favorite: explicit cursor into an array based on the cursor%rowtype. Explicit cursors give you querying flexibility, and if the SELECT clause changes, you don't have to rework the bulk fetch.

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

Analysis: In 9.2, there's no need to use parallel scalar arrays anymore. It's best to migrate up to at least 9.2.0.3.0 (I think 9.2.0.4.0 is the latest patchset), which adds features.
 

This was first published in July 2004

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close