Bulk DML with the FORALL Statement

Oracle introduced a significant enhancement to PL/SQL's DML capabilities for Oracle8i and above with the FORALL statement. FORALL tells the PL/SQL runtime engine to bulk bind into the SQL statement all of the elements of one or more collections before sending anything to the SQL engine. Why would this be useful? 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 though you can now (at least theoretically) use Java inside the database as well.

But this tight integration does not necessarily mean that no overhead is 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 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 13-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. In Oracle8i and above, Oracle 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. These enhancements are FORALL, which is explored in this chapter, and BULK COLLECT, which is explained in Chapter 14.

Figure 13-1. Context switching between PL/SQL and SQL

When the statement is bulk bound and passed to SQL, the SQL engine executes the statement once for each index number in the range. In other words, the same SQL statements are executed, but they are all run in the same round trip to the SQL layer, minimizing the context switches. This is shown in Figure 13-2.