Tuesday, November 15, 2005

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.

13 comments:

  1. Hello,
    You could also see more information about collections in the following article:
    http://fdegrelle.over-blog.com/article-1192806.html

    ReplyDelete
  2. I NEED UR HELP!!
    Could u pls shed light on my query...
    I have table wid 164 columns and 460,578 records stored in it.
    one of my stored procedures requires 54 column of the table to be intialised to null before processing...

    I tried using a curosr and a update statement to intialize the same...but performance is low...how can i improve performance using bulk update???

    ReplyDelete
  3. The fastest way to make an update is to use a single update statement. You do not need plsql for this I think.

    ReplyDelete
  4. hey can I have your email ^^

    ReplyDelete
  5. Has any body given solution for the question "I have table wid 164 columns and 460,578 records stored in it.
    one of my stored procedures requires 54 column of the table to be intialised to null before processing...
    " as iam facing the same problem.
    Can you please let me know if there is any solution.

    ReplyDelete
  6. It depends on what you are trying to achieve. A single update statement can do the work.

    ReplyDelete
  7. I think there's a bug if there are no rows in the last fetch.

    ReplyDelete
  8. Hi all,

    If the fetch command returns with no lines, the next command “for i in 1.. rec1.count loop” wil not work, because “rec1.count” variable is set to 0, and if you have the condition “for 1 to 0” an error occur.

    To solv this problem include an “if” condition with “rec1.count >= 1” before the “loop” command.

    open c1;
    7 loop
    8 fetch c1 bulk collect into rec1 limit 200;
    if rec1.count >= 1 then -- include this condition here.
    9 for i in 1..rec1.count loop
    10 null;
    11 end loop;
    end if;
    12 exit when c1%notfound;
    13 end loop;
    14

    Regards,
    André Borges.

    ReplyDelete
  9. André, it is not true. Fetching 0 rows does not produce an error. My example has a notfound control after the loop, when the fetch gets 0 rows the code exits the loop. "for 1 to 0" does not produce an error.

    Try my example for a table with 200 or 400 rows. Examples below:


    YAS@10G>r
    1 begin
    2 for i in 1..0 loop
    3 dbms_output.put_line(i);
    4 end loop;
    5* end;

    PL/SQL procedure successfully completed.

    YAS@10G>create table t as select * from all_objects where rownum<=200;

    Table created.

    YAS@10G>r
    1 declare
    2 cursor c1 is select * from t;
    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 dbms_output.put_line('Fetched count='||rec1.count);
    10 for i in 1..rec1.count loop
    11 null;
    12 end loop;
    13 exit when c1%notfound;
    14 end loop;
    15
    16* end;
    Fetched count=200
    Fetched count=0

    PL/SQL procedure successfully completed.

    ReplyDelete
  10. Hello, Yasin!

    By November 2005 I helped you reviewing a paper of yours about bulk collects.

    Nevertheless, I can't find in my stuff the paper.

    Do you still have it? Could you send me it online to lfcerri at gmail dot com?

    Thanks a lot!

    ReplyDelete
  11. You can not use %not found in bulk collect and limit.
    If the last call did not have enough records to fill the collection up to the limit, %not found will be on.
    You need to compare the count against the limit and exit when count is less then limit.

    ReplyDelete
  12. Yechiel, %notfound works. It is correct that it will be on when the number of rows fetched is less than the limit. That's why I we put it just before the end loop statement, not right after the fetch statement.

    Another example:


    SQL> declare
    2 cursor c1 is select rownum r from all_objects where rownum<=10;
    3 type c1_rec is table of c1%rowtype;
    4 rec1 c1_rec;
    5 begin
    6 open c1;
    7 loop
    8 fetch c1 bulk collect into rec1 limit 6;
    9 for i in 1..rec1.count loop
    10 dbms_output.put_line('Row='||rec1(i).r);
    11 end loop;
    12 exit when c1%notfound;
    13 end loop;
    14 end;
    15 /
    Row=1
    Row=2
    Row=3
    Row=4
    Row=5
    Row=6
    Row=7
    Row=8
    Row=9
    Row=10

    PL/SQL procedure successfully completed.

    ReplyDelete