Oracle PL/SQL Programming Guide to Oracle 8i Features

Oracle PL/SQL Programming Guide to Oracle 8i FeaturesSearch this book
Previous: 4.7 NDS Utility PackageChapter 5Next: 5.2 Bulk DML with the FORALL Statement

5. Bulking Up with PL/SQL 8.1

Context-Switching Problem Scenarios
Bulk DML with the FORALL Statement
Bulk Querying with the BULK COLLECT Clause
Using Cursor Attributes
Analyzing the Impact of Bulk Operations

We all know that PL/SQL is tightly integrated with the underlying SQL engine in the Oracle database. PL/SQL is the database programming language of choice for Oracle -- even if you now can at least theoretically use Java inside the database as well.

But this tight integration does not necessarily mean that there isn't any overhead associated with running SQL from a PL/SQL program. When the PL/SQL runtime engine processes a block of code, it executes the procedural statements within its own engine, but it passes the SQL statements on to the SQL engine. The SQL layer executes the SQL statements and then returns information to the PL/SQL engine, if necessary.

This transfer of control (shown in Figure 5.1) between the PL/SQL and SQL engines is called a context switch. Each time a switch occurs, there is additional overhead. There are a number of scenarios in which many switches occur and performance degrades. Oracle 8.1 now offers two enhancements to PL/SQL that allow you to bulk together multiple context switches into a single switch, thereby improving the performance of your applications.

Figure 5.1: Context switching between PL/SQL and SQL

Figure 5.1

These new features are as follows:


A variation on the FOR loop that bundles together multiple DML statements based on data in a collection


An enhancement to implicit and explicit query cursor syntax that allows the transfer of multiple rows of data in a single round-trip between the PL/SQL and SQL engines

5.1 Context-Switching Problem Scenarios

Before we take a look at the details of FORALL and BULK COLLECT, let's examine the scenarios where excessive context switches are likely to cause problems. These are likely to happen when you are processing multiple rows of information stored (or to be deposited) in a collection (a VARRAY, nested table, index-by table, or host array).

Suppose, for example, that I have filled two variable arrays with war criminals' ID numbers and the latest count of their victims. I then want to update the war criminals table with this information. Here's the solution I would have written prior to Oracle 8.1 (referencing a couple of already defined variable arrays):

   warcrim_ids IN name_varray,
   num_victims IN number_varray
   FOR indx IN warcrim_ids.FIRST .. warcrim_ids.LAST 
      UPDATE war_criminal
         SET victim_count = num_victims (indx)
       WHERE war_criminal_id = warcrim_ids (indx);

If I needed to update 100 rows, then I would be performing 100 context switches, since each update is processed in a separate trip to the SQL engine. Figure 5.2 illustrates this excessive (but previously unavoidable) switching.

Figure 5.2: Excessive context switching for multiple UPDATEs

Figure 5.2

You can also run into lots of switching when you fetch multiple rows of information from a cursor into a collection. Here is an example of the kind of code that cries out for the Oracle 8.1 bulk collection feature:

   CURSOR major_polluters IS
      SELECT name, mileage
        FROM cars_and_trucks
       WHERE vehicle_type IN ('SUV', 'PICKUP');
   names name_varray := name_varray();
   mileages number_varray := number_varray();
   FOR bad_car IN major_polluters
      names (major_polluters%ROWCOUNT) :=;
      mileages (major_polluters%ROWCOUNT) := bad_car.mileage;

   ... now work with data in the arrays ...

If you find yourself writing code like either of the previous examples, you will be much better off switching to one of the bulk operations explored in the following sections. In particular, you should keep an eye out for these cues in your code:

Previous: 4.7 NDS Utility PackageOracle PL/SQL Programming Guide to Oracle 8i FeaturesNext: 5.2 Bulk DML with the FORALL Statement
4.7 NDS Utility PackageBook Index5.2 Bulk DML with the FORALL Statement

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference