Using Bulk Binds And FORALL
See these important notes on bulk collect and forall:
By Mark Rittman:
January 31,
2004
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.
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 Feuerstein 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.
and include the URL for the page.
