Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  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
  3    type string_array is varray(10000) of  
  5    a_store string_array;
  6    a_qty   number_array;
  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; /


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  / 


While a bulk collect  retrieves data in bulk, the bulk load will change data in bulk.

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.