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):

CREATE OR REPLACE PROCEDURE update_tragedies (
   warcrim_ids IN name_varray,
   num_victims IN number_varray
   )
IS
BEGIN
   FOR indx IN warcrim_ids.FIRST .. warcrim_ids.LAST 
   LOOP
      UPDATE war_criminal
         SET victim_count = num_victims (indx)
       WHERE war_criminal_id = warcrim_ids (indx);
   END LOOP;
END;

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

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:

DECLARE
   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();
BEGIN
   FOR bad_car IN major_polluters
   LOOP
      names.EXTEND;
      names (major_polluters%ROWCOUNT) := bad_car.name;
      mileages.EXTEND;
      mileages (major_polluters%ROWCOUNT) := bad_car.mileage;
   END LOOP;

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

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:

• A recurring SQL statement inside a PL/SQL loop (it doesn't have to be a FOR loop, but that is the most likely candidate).

• Some parameter that can be made a bind variable. You need to be able to load those values into a collection to then have it processed by FORALL.