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

Home
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


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

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
                                           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.


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 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm


Download your Oracle scripts now:

www.oracle-script.com

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.