Using Bulk Binds And Oracle 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, Oracle BULK_COLLECT
and Oracle 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,
Oracle BULK COLLECT and
Oracle 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
Oracle 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 Oracle 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
Oracle 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, Oracle BULK
COLLECT and Oracle FORALL are the
new features in Oracle 8i,
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
by mark at
09:01 AM |
Comments (1)