 |
|
Oracle Tips by Burleson |
The
PL/SQL Bulk Collect Operation
Normally 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 Oracle10g database, the PL/SQL engine may
perform a bulk collect for you. In 10g, 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.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
