Using Bulk Binds And FORALL

Yesterday, I looked at collections, an evolution of PL/SQL tables that allows us to manipulate many variables at once, as a unit. Collections, coupled with two new features introduced with Oracle 8i, BULK_COLLECT and FORALL, can dramatically increase the performance of data manipulation code within PL/SQL.

As a reminder, we were looking at a piece of code that implemented collections, BULK COLLECT and FORALL, taken from a question and answer posed online by Daniel Morgan.

CREATE OR REPLACE PROCEDURE fast_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS

TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS
SELECT *
FROM all_objects;

BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO t2 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END fast_proc;
/

which was subsequently refined in a later answer to;

SQL> create or replace procedure fast_proc is
2         type TObjectTable is table of ALL_OBJECTS%ROWTYPE;
3         ObjectTable$ TObjectTable;
4         begin
5         select
6                     * BULK COLLECT INTO ObjectTable$
7         from ALL_OBJECTS;
8
9         forall x in ObjectTable$.First..ObjectTable$.Last
10       insert into t1 values ObjectTable$(x) ;
11       end;
12 /

The key things here are the collection that is set up to hold the table data, the BULK COLLECT clause and the FORALL statement. I went into detail about the collection yesterday, so now it's time to look at the other two.

Steven Feuernstein explains the basics behind BULK_COLLECT and FORALL in a recent OTN article, and together these two features are known as 'Bulk Binding'. Bulk Binds are a PL/SQL technique where, instead of multiple individual SELECT, INSERT, UPDATE or DELETE statements are executed to retrieve from, or store data in, at table, all of the operations are carried out at once, in bulk. This avoids the context-switching you get when the PL/SQL engine has to pass over to the SQL engine, then back to the PL/SQL engine, and so on, when you individually access rows one at a time. To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement. To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

According to the online documentation, BULK COLLECT is explained as;

"The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses. Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ..."

and FORALL is defined as

"The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:

FORALL index IN lower_bound..upper_bound
   sql_statement;

The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range."

So there you go. Collections, BULK COLLECT and FORALL are the new features in Oracle 8i, 9i and 10g PL/SQL that can really make a different to you PL/SQL performance. Hopefully, if you've not come across these areas before, this has been of some use - certainly through writing this up I've found out quite a few things I'd only heard of in passing before. Lastly, if you want to read a good article on bulk binding and FORALL, take a look at "Oracle 10g Adds More to FORALL" by Steven Feuerstein.