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 FORALL Tips

Oracle Tips by Burleson Consulting
Don Burleson

By Mark Rittman

Using Bulk Binds And Oracle FORALL

Yesterday, I looked at collections, an evolution of PL/SQL tables that allows us to manipulate many variables at once, as a unit. Collections, coupled with two new features introduced with Oracle 8i, Oracle BULK_COLLECT and Oracle FORALL, can dramatically increase the performance of data manipulation code within PL/SQL.

As a reminder, we were looking at a piece of code that implemented collections, Oracle BULK COLLECT and Oracle FORALL, taken from a question and answer posed online by Daniel Morgan.

CREATE OR REPLACE PROCEDURE fast_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS

TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS
SELECT *
FROM all_objects;

BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO t2 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END fast_proc;
/

which was subsequently refined in a later answer to;

SQL> create or replace procedure fast_proc is
2         type TObjectTable is table of ALL_OBJECTS%ROWTYPE;
3         ObjectTable$ TObjectTable;
4         begin
5         select
6                     * BULK COLLECT INTO ObjectTable$
7         from ALL_OBJECTS;
8
9         forall x in ObjectTable$.First..ObjectTable$.Last
10       insert into t1 values ObjectTable$(x) ;
11       end;
12 /

The key things here are the collection that is set up to hold the table data, the BULK COLLECT clause and the Oracle FORALL statement. I went into detail about the collection yesterday, so now it's time to look at the other two.

Steven Feuernstein explains the basics behind BULK_COLLECT and Oracle FORALL in a recent OTN article, and together these two features are known as 'Bulk Binding'. Bulk Binds are a PL/SQL technique where, instead of multiple individual SELECT, INSERT, UPDATE or DELETE statements are executed to retrieve from, or store data in, at table, all of the operations are carried out at once, in bulk. This avoids the context-switching you get when the PL/SQL engine has to pass over to the SQL engine, then back to the PL/SQL engine, and so on, when you individually access rows one at a time. To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement. To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

According to the online documentation, BULK COLLECT is explained as;

"The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses. Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ..."

and Oracle FORALL is defined as

"The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:

FORALL index IN lower_bound..upper_bound
   sql_statement;

The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range."
 

So there you go. Collections, Oracle BULK COLLECT and Oracle FORALL are the new features in Oracle 8i, and 10g PL/SQL that can really make a different to you PL/SQL performance. Hopefully, if you've not come across these areas before, this has been of some use - certainly through writing this up I've found out quite a few things I'd only heard of in passing before. Lastly, if you want to read a good article on bulk binding and FORALL, take a look at "Oracle 10g Adds More to FORALL" by Steven Feuerstein.

Posted by mark at 09:01 AM | Comments (1)


 

 



 

 

??
 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

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.