Reducing Loop Overhead for Collections with Bulk Binds in Oracle

The following figure shows you the relationship between the PL/SQL engine and SQL Engine when excecuting an PL/SQL statement block: PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine, which executes the SQL statements and, in some cases, returns data to the PL/SQL engine.

From the above figure, you should know that too many context switches between the PL/SQL and SQL engines will harm performance. That can happen when a loop executes a separate SQL statement for each element of a collection, specifying the collection element as a bind variable. For example, the following DELETE statement is sent to the SQL engine with each iteration of the FOR loop:

DECLARE
   TYPE NumList IS VARRAY(20) OF NUMBER;
   depts NumList := NumList(10, 30, 70);  -- department numbers
BEGIN
   ...
   FOR i IN depts.FIRST..depts.LAST LOOP
      DELETE FROM emp WHERE deptno = depts(i);
   END LOOP;
END;

In such cases, if the SQL statement affects four or more database rows, the use of bulk binds can improve performance considerably.

What Is Bulk Binds?

The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:

• in-bind: When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
• out-bind: When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
• define: When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.

A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding. For example, if the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth.

• To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement.
• To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.

Example: Performing a Bulk Bind with SELECT

In the next example, the SQL engine loads all the values in an object column into a nested table before returning the table to the PL/SQL engine:

CREATE TYPE Coords AS OBJECT (x NUMBER, y NUMBER);
CREATE TABLE grid (num NUMBER, loc Coords);
INSERT INTO grid VALUES(10, Coords(1,2));
INSERT INTO grid VALUES(20, Coords(3,4));

DECLARE
   TYPE CoordsTab IS TABLE OF Coords;
   pairs CoordsTab;
BEGIN
   SELECT loc BULK COLLECT INTO pairs FROM grid;
   -- now pairs contains (1,2) and (3,4)
END;

You can bulk-fetch from a cursor into one or more collections:

DECLARE
   TYPE NameList IS TABLE OF emp.ename%TYPE;
   TYPE SalList IS TABLE OF emp.sal%TYPE;
   CURSOR c1 IS SELECT ename, sal FROM emp WHERE sal > 1000;
   names NameList;
   sals  SalList;
BEGIN
   OPEN c1;
   FETCH c1 BULK COLLECT INTO names, sals;

Example: Performing a Bulk Bind with UPDATE

As the following example shows, the bounds of the FORALL loop can apply to part of a collection, not necessarily all the elements:

DECLARE
   TYPE NumList IS VARRAY(10) OF NUMBER;
   depts NumList := NumList(20,30,50,55,57,60,70,75,90,92);
BEGIN
   FORALL j IN 4..7  -- bulk-bind only part of varray
      UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(j);
END;