Several approaches to bulk collection

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

    Requires Free Membership to View

    Login 

 (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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top