Oracle Bulk Collect
One method of fetching data is an Oracle bulk collect.
With Oracle bulk collect, the PL/SQL engine tells the SQL engine to
collect many rows at once and place them in a collection. During
an Oracle bulk collect, the SQL engine retrieves all the rows and loads
them into the collection and switches back to the PL/SQL engine.
When rows are retrieved using Oracle bulk collect, they are retrieved with only
two context switches. The larger the number of rows
you would like to collect with Oracle bulk collect, the more
performance improvement you will see using an Oracle bulk collect.
For more information on Oracle bulk collect see my additional notes
below:
Using PL/SQL Bulk Collects
Bulk Operations, Packages and Triggers
Starting in Oracle10g, an Oracle bulk collect may be
performed by the the PL/SQL engine for you. The PL/SQL
engine may automatically use Oracle bulk collect to collect 100 rows at a time
because of a cursor loop.
This use of Oracle bulk collect allows your
code to process rows without having to setup and execute the Oracle bulk
collect operation. The result of this use of Oracle bulk
collect is that bulk collecting 75 rows may not provide you with much of a
benefit, but using Oracle bulk collect to collect large numbers of rows (many hundreds)
will provide increased performance.
Bulk collect is easy to use.
First, define the collection or collections that will be collected using the
Oracle bulk collect. Next,
define the cursor to retrieve the data in the Oracle bulk collect. Finally, bulk collect the data
into the collections.
Aa simple
Oracle bulk collect example is shown below:
The PL/SQL Bulk Collect
OperationNormally a developer will
use a cursor to retrieve and process multiple rows of data, one at a
time, but there are performance problems when dealing with large numbers
of rows using cursors. As we have seen, a cursor fetches one row at a
time, holding a consistent view, until all rows have been retrieved or
the cursor is closed.
A performance issue arises from the fact that there
are two engines in the database, the PL/SQL engine and the SQL engine.
In some versions of the database, these engines actually have different
capabilities resulting in some features being available in SQL but not
in PL/SQL. When a cursor fetches a row of data it performs a "context
switch" to the SQL engine, and it is the SQL component that retrieves
the data. The SQL engine places the data in-memory and another context
switch places us back into the PL/SQL engine.
The PL/SQL engine then continues processing until
the next row is required, and the process repeats. A context switch is
very fast, but if performed over and over again, the constant switching
can take a noticeable amount of time. A bulk collect is a method of
fetching data where the PL/SQL engine tells the SQL engine to collect
many rows at once and place them in a collection. The SQL engine
retrieves all the rows and loads them into the collection and switches
back to the PL/SQL engine. All the rows are retrieved with only 2
context switches. The larger the number of rows processed, the more
performance is gained by using a bulk collect.
In the Oracle database, the PL/SQL engine may
perform a bulk collect for you. In 10g and beyond, a cursor loop may cause the
PL/SQL engine to automatically bulk collect 100 rows at a time, allowing
your code to process rows without having to setup and execute the bulk
collect operation. As a result of this performance enhancement in 10g,
bulk collecting 75 rows may not provide you with much of a benefit,
while bulk collecting large numbers of rows (many hundreds) will still
provide you with increased performance.
Bulk collecting data is easy. First, we define the
collection or collections that will be used in the bulk collect. Next,
define the cursor to retrieve the data and finally, bulk collect the
data into the collections. The example below demonstrates a simple bulk
collect:
SQL> declare
2
type number_array is varray(10000) of
number;
3 type string_array
is varray(10000) of
varchar2(100);
4
5 a_store string_array;
6 a_qty number_array;
7
8 cursor c1 is
9 select store_key,
sum(quantity) from sales
10 group by store_key;
11 begin
12
open c1;
13 fetch c1 bulk collect into a_store, a_qty;
14 close c1;
15 for indx in a_store.first..a_store.last loop
16
dbms_output.put_line(
17 a_store(indx)||'....'||a_qty(indx));
18
end loop;
19 end; /
S102....21860
S105....13000
S109....12120
S101....2180
S106....6080
S103....7900
S104....13700
S107....24700
S108....5400
S110....3610
There is nothing new in the above example except
line 13. Here instead of fetching one row, we bulk collect all the rows
at once. Notice that this also allowed us to close the cursor and free
the database resources it had obtained immediately after collecting the
rows. The data can now be processed as needed in memory. In this
example, lines 16 and 17 print out the data from the in-memory
collection.
Also, note that the last example also used two
arrays. The arrays were loaded together in the one BULK COLLECT INTO
statement. As the SQL engine loaded the arrays, it places the data at
the same index location in each array. If a store key existed in the
table with no orders (not the case in this example), the store key would
be place in the a_store array and a null would be placed in the
a_qty array. This allows you to search the store array and
retrieve the qty using the store array index. This is demonstrated in
lines 15 through 18 in the example.
In database versions 9iR2 and later, you can bulk
collect into records:
SQL> declare
2 type sales_tab is table of sales%rowtype;
3 t_sal sales_tab;
4
begin
5 select * bulk collect into t_sal from sales;
6
dbms_output.put_line(t_sal.count);
7 end;
8 /
100
While a bulk collect retrieves data in bulk, the
bulk load will change data in bulk.
and include the URL for the page.
