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

 
 Home
 E-mail Us
 Oracle Articles
New 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 


 

 

 


 

 

 
 

Collections, Cursors, Bulk Binds and FORALL

Oracle Tips by Burleson Consulting
Don Burleson

By Mark Rittman

Collections, Cursors, Bulk Binds and FORALL

Once in a while, I read something about Oracle that stops me in my tracks and makes me really think about how I approach my job. Recent examples include starting to work with AS, and slowly becoming aware of how much I'm going to have to get my head around the role Java and middleware is going to have in future Oracle applications. Another was when I began studying for my OCP, and began to understand how, for any system to be effective, you need to really have a good understanding of how Oracle works internally. The latest example came about from reading a recent thread started by Daniel Morgan on comp.databases.oracle.server.

The initial posting asked the question:

"At a class I taught this last weekend I brought up the fact that most PL/SQL programmers are still writing v7 code. I gave everyone there a challenge and thought I'd share it with the group for any of you looking for a challenge on which to sharpen your skills.

CREATE TABLE t1 AS
SELECT *
FROM all_objects
WHERE 1=0;

CREATE OR REPLACE PROCEDURE test_proc IS

BEGIN
    FOR x IN (SELECT * FROM all_objects)
    LOOP
        INSERT INTO t1
        (owner, object_name, subobject_name, object_id,
        data_object_id, object_type, created, last_ddl_time,
        timestamp, status, temporary, generated, secondary)
        VALUES
        (x.owner, x.object_name, x.subobject_name, x.object_id,
        x.data_object_id, x.object_type, x.created,
        x.last_ddl_time, x.timestamp, x.status, x.temporary,
        x.generated, x.secondary);
    END LOOP;
COMMIT;
END test_proc;
/

set timing on
exec test_proc;
set timing off

Everyone using 8i+ features should be able to improve the performance of
this by at least 5X.

I'll post a solution in a week or so."

The bit that hit home was the comment about most PL/SQL programmers still writing v7 code. Thinking about it, that's one I'm guilty of.

The sort of work I do involves knowing as much as possible about as many Oracle products as possible. One week I'm tuning up a Discoverer installation, next week I'm building a data model for a first-phase data warehouse. Large parts of my work involve working out which Oracle products are best suited to a potential application, and the nature of the job is that you thoroughly learn something for a particular project, then move on and rely on that knowledge for some time afterwards. On average, I usually know more about a particular Oracle product than most people, but I'm the first to admit that I'm no expert and there's always room to learn.

As Daniel Morgan points out, PL/SQL has come on considerably since version 2.3 that came with Oracle 7. One of the major areas of improvement has been in the area of arrays and PL/SQL Tables, and in fact this area is now referred to in Oracle 8i, and now 10g as 'Collections'. Together with the way cursors are now handled, there's now much more efficient ways of bulk processing large arrays of data, and it's worth taking some time out to look at how things have developed.

Going back to the original thread, and discarding the approach of just using a straight insert (*/ append */)  into ... select ... from all_objects (which of course would be the fastest, as it's just doing the insert using a straight SQL set operation) , the answer as provided by Daniel was as follows;

"I was thinking in terms of some variation on the following:

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;
/

of which many possibilities exist. One of which Billy V posted. The point I would hope more junior developers take away from this is that while cursors definitely have their uses ... they should not be the first thing one thinks of any more. Ok ... shouldn't be the first thing after the 'obvious' DML statement."

Another solution proposed by Billy Verreynne was even more compact, and gave a threefold increase in performance.

"My attempt gives me a 3x improvement in performance....

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 /

Procedure created."

So, what are the new features that the two solutions are using, and why do they give such an increase in performance? I decided to take a bit of time out and do some more studying.

The first place to look for details of current PL/SQL syntax is the online manuals available at docs.oracle.com. In this instance, the document we're after is the PL/SQL User's Guide and Reference Release 2 (9.2), and in particular, the sections on PL/SQL Collections and Records, Managing Cursors., Bulk Binds and the FORALL statement. I also took a look at a book we got hold of recently, "Oracle PL/SQL Programming", by Scott Urman.

Over the next few days, I'll be looking at each of these areas in turn, starting tomorrow with PL/SQL Collections and Records.

 



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational