Using sparse collections with FORALL

FORALL is a key performance feature of PL/SQL. It helps you avoid row-by-row processing of non-query DML (insert, update, delete, merge) from within a PL/QL block. Best of all, almost always, is to do all your processing entirely within a single SQL statement. Sometimes, however, that isn't possible (for example, you need to sidestep SQL's "all or nothing" approach) or simply too difficult (not all of us have the insane SQL writing skills of a Tom Kyte or a Chris Saxon or a Connor McDonald).

To dive in deep on FORALL, check out any of the following resources:

• FORALL documentation
• Videos at Practically Perfect PL/SQL 
• Tim Hall on Bulk Binds

In this post, I am going to focus on special features of FORALL that make it easy to work with space collections: the INDICES OF and VALUES OF clauses.

Typical FORALL Usage with Dense Bind Array

Here's the format you will most commonly see with FORALL: the header looks just like a numeric FOR loop, but notice: no loop keywords. Two rows will be updated, because the collection is filled sequentially or densely: every index value between the lowest and the highest are defined.

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;
   l_employees   employee_aat;
BEGIN
   l_employees (1) := 7839;
   l_employees (2) := 7654;

   FORALL l_index IN 1 .. l_employees.COUNT
      UPDATE employees SET salary = 10000
       WHERE employee_id = l_employees (l_index);
END;
/

When We Go Sparse...

But take a close look at the way I assign values in the next block. Now my lowest index value is 1 and my highest is 100, with nothing in between. This is known as a sparse collection.

Now when I run the same code, I get an error: ORA-22160: element at index [2] does not exist.

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;
   l_employees   employee_aat;
BEGIN
   l_employees (1) := 7839;
   l_employees (100) := 7654;

   FORALL l_index IN 1 .. l_employees.COUNT
      UPDATE employees SET salary = 10000
       WHERE employee_id = l_employees (l_index);
END;
/

ORA-22160: element at index [2] does not exist

Notice that this is a SQL error, not a PL/SQL exception (if the latter, we might have predicted that the ORA-01403 No data found might have been raised): the collection was passed to the SQL engine, the SQL engine tried to go from first to last, incrementing the counter each time - and then it blew up.

When you are trying to use FORALL with a sparse collection, you must do one of the following:

1. "Densify" the collection - get rid of the gaps. This was necessary prior to Oracle Database 10g. Hopefully that means you can ignore this option.
2. Use INDICES OF 
3. Use VALUES OF

Simplest INDICES OF Use Case

INDICES OF is the solution you will most likely use. Use this approach when you have a collection (the indexing array) whose defined index values can be used to specify the index values in the bind array (referenced within the FORALL's DML statement) that are to be used by FORALL.

In other words, if the element at index value N is not defined in the indexing array, you want the FORALL statement to ignore the element at position N in the bind array.

And in the simplest use case of INDICES OF, the indexing and bind arrays are the same, as you see in the example below.

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;
   l_employees   employee_aat;
BEGIN
   l_employees (1) := 7839;
   l_employees (2) := 7654;

   FORALL l_index IN INDICES OF l_employees
      UPDATE employees SET salary = 10000
       WHERE employee_id = l_employees (l_index);
END;
/

This simply says: only use the defined index values of l_employees, and skip over any gaps. Nice!

More Interesting INDICES OF Usage

But you can do more with INDICES OF than that.

Suppose your bind array has 10,000 elements defined in it. You need to perform three different FORALL operations against different subsets of those elements.

You could copy the selected contents required for each FORALL "run" into its own collection. But that could use more PGA memory then necessary. You could instead construct three different indexing arrays, each of which simply point back to elements in the bind array that are relevant for that run.

In the example below, the l_employee_indices is my indexing array. Notice that the actual contents of each element in this array is of no importance. The PL/SQL engine will look only at the index values.

Notice that I can also use a BETWEEN clause to restrict which index values I want to use. So in this block, I update the rows for employee IDs 7839 and 7950 only.

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;
   l_employees           employee_aat;

   TYPE boolean_aat IS TABLE OF BOOLEAN
      INDEX BY PLS_INTEGER;

   l_employee_indices   boolean_aat;
BEGIN
   l_employees (1) := 7839;
   l_employees (100) := 7654;
   l_employees (500) := 7950;
   
   l_employee_indices (1) := TRUE;
   l_employee_indices (500) := TRUE;
   l_employee_indices (799) := TRUE;

   FORALL l_index IN INDICES OF l_employee_indices
        BETWEEN 1 AND 500
     UPDATE employees             
        SET salary = 10000
      WHERE employee_id = l_employees (l_index);
END;
/

And Then There is VALUES OF

I've met lots of developers over the years who have used INDICES OF. I've not yet encountered anyone who took advantage of VALUES OF. So if you ever do find a use for it in your code, please let me know! :-)

Use this clause when you have a collection of integers (again, the indexing array) whose content (the value of the element at a specified position) identifies the position in the binding array that you want to be processed by the FORALL statement.

So while with INDICES OF, the PL/SQL engine uses the index values of the indexing array, with VALUES OF, it uses the values of the elements in the collection.

Here's an example:

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;

   l_employees           employee_aat;

   TYPE indices_aat IS TABLE OF PLS_INTEGER
      INDEX BY PLS_INTEGER;

   l_employee_indices   indices_aat;
BEGIN
   l_employees (-77) := 7820;
   l_employees (13067) := 7799;
   l_employees (99999999) := 7369;
   
   l_employee_indices (100) := −77;
   l_employee_indices (200) := 99999999;
   
   FORALL l_index IN VALUES OF l_employee_indices
      UPDATE employees
         SET salary = 10000
       WHERE employee_id = l_employees (l_index);
END;
/

I populate (sparsely) three rows (–77, 13067, and 99999999) in the collection of employee IDs.

I want to set up the indexing array to identify which of those rows to use in my update. Because I am using VALUES OF, the row numbers that I use are unimportant. Instead, what matters is the value found in each of the rows in the indexing array. Again, I want to skip over that “middle” row of 13067, so here I define just two rows in the l_employee_indices array and assign them values –77 and 9999999, respectively.

Rather than specify a range of values from FIRST to LAST, I simply specify VALUES OF l_employee_indices. Notice that I populate rows 100 and 200 in the indices collection. VALUES OF does not require a densely filled indexing collection.

VALUES OF also does not support a BETWEEN clause like INDICES OF.

So VALUES OF gives you lots of flexibility - perhaps more than you will ever need!

Sparse is Fine with FORALL

So remember: it's no problem using the powerful FORALL feature with sparse collections. All you have to do is pick between INDICES OF or VALUES OF, and let the PL/SQL do all (or more) of the work for you.

Here are LiveSQL scripts covering much the same material as shown above:

INDICES OF
VALUES OF