Bulk Bind

Overview

This article contains information on bulk binding - what it is, the
advantages of using it, and how to use it.


What is Bulk Binding?


The assignment of values to PL/SQL variables in SQL statements is
called binding.  The binding of an entire collection at once is
referred to as bulk binding.


What is the Advantage?


Bulk binds improve performance by minimizing the number of context
switches between PL/SQL and SQL engines while they pass an entire collection
of elements (varray, nested tables, index-by table, or host array) as
bind variables back and forth.
   
Prior to Oracle8i, the execution of every SQL statement required a switch
between the PL/SQL and SQL engines, whereas bulk binds use only one context
switch.   
How to Bulk Bind


Bulk binding includes the following:

(i)  Input collections, use the FORALL statement

(ii) Output collections, use BULK COLLECT clause


(i) Input Collections


Input collections are data passed from the PL/SQL engine to the SQL
engine to execute INSERT, UPDATE, and DELETE statements.

The following is the syntax for the FORALL statement:

   FORALL index IN lower_bound..upper_bound
     sql_statement;


Example 1


In this example, 5000 part numbers and names are loaded into index-by
tables.  Then, all table elements are inserted into a database table
twice.  First, they are inserted using a FOR loop, which completes in
8 seconds.  Then, they are inserted using a FORALL statement, which
completes in only 0 seconds.

SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));
   
DECLARE
  TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
   TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
   pnums NumTab;
   pnames NameTab;
   t1 CHAR(5);
   t2 CHAR(5);
   t3 CHAR(5);
   PROCEDURE get_time(t OUT NUMBER) IS
   BEGIN SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual; END;
BEGIN
   FOR j IN 1..5000 LOOP  -- load index-by tables
      pnums(j) := j;
      pnames(j) := 'Part No. ' || TO_CHAR(j);
   END LOOP;
   get_time(t1);
   FOR i IN 1..5000 LOOP -- use FOR loop
      INSERT INTO parts VALUES (pnums(i), pnames(i));
   END LOOP;
   get_time(t2);
   FORALL i IN 1..5000  --use FORALL statement
      INSERT INTO parts VALUES (pnums(i), pnames(i));                        
   get_time(t3);
   DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
   DBMS_OUTPUT.PUT_LINE('---------------------');
   DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR(t2 - t1));
   DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR(t3 - t2));
END;
/                                                                             

SQL> @bulk.sql
Execution Time (secs)
---------------------
FOR loop: 8
FORALL:   0

PL/SQL procedure successfully completed.              


Rollback Behavior


If a FORALL statement fails, then database changes are rolled back to an
implicit savepoint marked before each execution of the SQL statement.


(ii) Output Collections


Output collections are data passed from the SQL engine to the PL/SQL engine
as a result of SELECT or FETCH statements.


Using the BULK COLLECT clause


The keywords BULK COLLECT can be used with SELECT INTO, FETCH INTO,
and RETURNING INTO clauses.  The syntax is as follows:

   ... BULK COLLECT INTO collection_name[, collection_name] ....


   Note:  Examples 2, 3, and 4 use EMP and DEPT tables from the
          scott/tiger schema.


Example 2


The following is an example for the SELECT INTO clause:

DECLARE
   TYPE NumTab IS TABLE OF emp.empno%TYPE;
   TYPE NameTab IS TABLE OF emp.ename%TYPE;
   enums NumTab; -- no need to initialize
   names NameTab;
BEGIN
   SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
   FOR i in enums.FIRST..enums.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(enums(i) || ' ' || names(i));
   END LOOP;
END;
/                                                                            


Example 3


The following is an example for the FETCH INTO clause:
  
DECLARE
   TYPE NameTab IS TABLE OF emp.ename%TYPE;
   TYPE SalTab IS TABLE OF emp.sal%TYPE;
   names NameTab;
   sals SalTab;
   CURSOR c1 IS SELECT ename, sal FROM emp;
BEGIN
   OPEN c1;


   FETCH c1 BULK COLLECT INTO names, sals;
   FOR i IN names.FIRST..names.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(names(i) || ' ' || sals(i));
   END LOOP;
   CLOSE c1;
END;
/                                                                             


Restriction


You cannot bulk-fetch from a cursor into a collection of records.   
                                                                         

New Cursor Attribute


The new composite cursor attribute for bulk binds is %BULK_ROWCOUNT.  Its
syntax is as follows:

   IF SQL%BULK_ROWCOUNT(i) = ... THEN
      .....
   ENDIF;


Example 4


DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10,20,50);
BEGIN
   FORALL i IN depts.FIRST..depts.LAST
      UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
   IF SQL%BULK_ROWCOUNT(3) = 0 THEN
   DBMS_OUTPUT.PUT_LINE('Its 3!!!');
   END IF;
END;
/