Bulk Collect

Executing sql statements in plsql programs causes a context switch between the plsql engine and the sql engine. Too many context switches may degrade performance dramatically. In order to reduce the number of these context switches we can use a feature named bulk binding. Bulk binding lets us to transfer rows between the sql engine and the plsql engine as collections. Bulk binding is available for select, insert, delete and update statements.

Bulk collect is the bulk binding syntax for select statements. All examples below are simplified versions of the live cases i have seen.

One of the things i usuallly come accross is that developers usually tend to use cursor for loops to process data. They declare a cursor, open it, fetch from it row by row in a loop and process the row they fetch.

Declare
Cursor c1 is select column_list from table_name>;
Rec1 c1%rowtype;
Begin
Open c1;
Loop;
Fetch c1 into r1;
Exit when c1%notfound;
--process rows...
End loop;
End;

Here is a simple test case to compare the performance of fetching row by row and using bulk collect to fetch all rows into a collection.

SQL> create table t_all_objects as select * from all_objects;

Table created.

SQL> insert into t_all_objects select * from t_all_objects;

3332 rows created.

SQL> r
1* insert into t_all_objects select * from t_all_objects

6664 rows created.

---replicated a couple of times

SQL> select count(*) from t_all_objects;

COUNT(*)
----------
213248

SQL> declare
cursor c1 is select object_name from t_all_objects;
2    3  rec1 c1%rowtype;
4  begin
5  open c1;
6  loop
7     fetch c1 into rec1;
8     exit when c1%notfound;
9
10     null;
11
12  end loop;
13  end;
14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:44.75

SQL> declare
2  cursor c1 is select object_name from t_all_objects;
3  type c1_type is table of c1%rowtype;
4  rec1 c1_type;
5  begin
6  open c1;
7
8     fetch c1 bulk collect into rec1;
9
10
11  end;
12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.32

As can be clearly seen, bulk collecting the rows shows a huge performance improvement over fetching row by row.

The above method (which fetched all the rows) may not be applicable to all cases. When there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again. Otherwise process memory gets bigger and bigger as you fetch the rows.

SQL> declare
2  cursor c1 is select object_name from t_all_objects;
3  type c1_type is table of c1%rowtype;
4  rec1 c1_type;
5  begin
6  open c1;
7  loop
8     fetch c1 bulk collect into rec1 limit 200;
9     for i in 1..rec1.count loop
10             null;
11     end loop;
12     exit when c1%notfound;
13  end loop;
14
15
16  end;
17  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.07

Detailed documentation for bulk collect can be found in Chapter 5 - PL/SQL User’s Guide and Reference.
The above examples are on 9.2.