This article begins a discussion
of how to work with collections. Previous versions of Oracle had limitations
for collection usage in regards to SQL statement processing. Bulk Bind and
Collect features were introduced to reduce the SQL processing overhead by
efficient use of collections in PL/SQL code.
The PL/SQL engine executes procedural statements and sends
all SQL statements present in the code to the SQL engine. The SQL engine will
parse and execute the query or DML statement and return the expected output
back to the PL/SQL engine. This switch between the two engines is called context
We mostly concentrate on the SQL statement to tune
performance issues. It is worth noting that excessive context switching can
affect performance. This would be substantially significant when we are
carrying out SQL statements in a loop. The features discussed below were
introduced to reduce this overhead of SQL processing. Introduced in Oracle 8i,
these features are being improved on with every new release.
Two PL/SQL features, Bulk Bind and Bulk collect
help in improving performance and utilizing resources effectively from within
PL/SQL code. These features reduce context switching, (i.e., multiple SQL
statements being executed from the code resulting in calls to SQL engine), and
carry out the operation on the concerned object at one go. Since the SQL
statements are fired in a bundle, frequent access to SQL engine is reduced.
In cases where the PL/SQL code is being executed on a
different terminal than the server itself, this would also result in optimal
network usage rather than too frequent network calls to access the SQL engine.
Bulk Collects (Reading data in bulk)
The bulk collect option instructs the SQL engine to bulk
bind the output collections before returning them to the PL/SQL engine. This
allows us to load data dynamically into collections at one shot for further
processing. Bulk collect can be used with SELECT INTO, FETCH INTO and RETURNING
... bulk collect into collection...
For example, let us assume that we need to load all pending
transactions into a temporary table and process them one by one. As part of validation,
there is a need to refer to the data in the same table, from time to time, for
each transaction being processed. One possible method to
write the code would be to load all of the data in the temporary table to a
collection type. This way, additional queries on the table could be avoided
(context switch) and the processing could be carried out in PL/SQL itself. This
idea is further improved on by the use of the bulk collect option, as all data
is loaded into PL/SQL at the same time.
type pndidr is table of mtl_pending_trx.trx_id%type index by binary_integer;
type pndqty is table of mtl_pending_trx.trx_qty%type index by binary_integer;
type pndval is table of mtl_pending_trx.trx_cost%type index by binary_integer;
select trx_id, trx_qty, trx_cost * trx_qty bulk collect
into l_pndidr, l_pndqty, l_pndval
for i in l_pndidr.first .. l_pndidr.last loop