Skip to main content

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

Comments

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel