Rittman Mead - Delivered Intelligence

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.

Posted in Oracle Database | 1 Comment »