Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle Bulk Collect tips

Oracle Tips by Burleson Consulting

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:

Bulk Collection of DML Results

How much PL/SQL context switching with bulk collect?

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

 

 


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.