Oracle PL/SQL for DBAs

Bulk DML with the FORALL Statement

Oracle introduced a significant enhancement to PL/SQL’s DML capabilities for Oracle8i Database 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 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. Starting with Oracle8i Database, Oracle 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 and BULK COLLECT ...