Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle

Oracle uses two engines to process PL/SQL code. All procedural code is handled by the PL/SQL engine while all SQL is handled by the SQL statement executor, or SQL engine.

There is an overhead associated with each context switch between the two engines. If PL/SQL code loops through a collection performing the same DML operation for each item in the collection it is possible to reduce context switches by bulk binding the whole collection to the DML statement in one operation.

BULK COLLECT

Bulk binds can improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. 

We can see the improvement associated with bulk operations to reduce context switches.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab t_bulk_collect_test_tab;

  CURSOR c_data IS

    SELECT *

    FROM bulk_collect_test;

BEGIN

  OPEN c_data;

  LOOP

    FETCH c_data

    BULK COLLECT INTO l_tab LIMIT 10000;

    EXIT WHEN l_tab.count = 0;

    -- Process contents of collection here.

    DBMS_OUTPUT.put_line(l_tab.count || ' rows');

  END LOOP;

  CLOSE c_data;

END;

/

10000 rows

10000 rows

10000 rows

10000 rows

2578 rows

PL/SQL procedure successfully completed.

FORALL

The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. 

SQL%BULK_ROWCOUNT

The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in the SQL%BULK_ROWCOUNT cursor attribute.

SAVE EXCEPTIONS and SQL%BULK_EXCEPTION

We saw how the FORALL syntax allows us to perform bulk DML operations, but what happens if one of those individual operations results in an exception? If there is no exception handler, all the work done by the current bulk operation is rolled back. If there is an exception handler, the work done prior to the exception is kept, but no more processing is done. Neither of these situations is very satisfactory, so instead we should use the SAVE EXCEPTIONS clause to capture the exceptions and allow us to continue past them. We can subsequently look at the exceptions by referencing the SQL%BULK_EXCEPTION cursor attribute. To see this in action create the following table.

CREATE TABLE exception_test (

  id  NUMBER(10) NOT NULL

);

The following code creates a collection with 100 rows, but sets the value of rows 50 and 51 to NULL. Since the above table does not allow nulls, these rows will result in an exception. TheSAVE EXCEPTIONS clause allows the bulk operation to continue past any exceptions, but if any exceptions were raised in the whole operation, it will jump to the exception handler once the operation is complete. In this case, the exception handler just loops through the SQL%BULK_EXCEPTION cursor attribute to see what errors occured.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();

  l_error_count  NUMBER;

  ex_dml_errors EXCEPTION;

  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);

BEGIN

  -- Fill the collection.

  FOR i IN 1 .. 100 LOOP

    l_tab.extend;

    l_tab(l_tab.last).id := i;

  END LOOP;

  -- Cause a failure.

  l_tab(50).id := NULL;

  l_tab(51).id := NULL;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.

  BEGIN

    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS

      INSERT INTO exception_test

      VALUES l_tab(i);

  EXCEPTION

    WHEN ex_dml_errors THEN

      l_error_count := SQL%BULK_EXCEPTIONS.count;

      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);

      FOR i IN 1 .. l_error_count LOOP

        DBMS_OUTPUT.put_line('Error: ' || i ||

          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||

          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

      END LOOP;

  END;

END;

/

Number of failures: 2

Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()

Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

SQL>

As expected the errors were trapped. If we query the table we can see that 98 rows were inserted correctly.

SELECT COUNT(*)

FROM   exception_test;

  COUNT(*)

----------

                  98

1 row selected.

SQL>

Bulk Binds and Triggers

For bulk updates and deletes the timing points remain unchanged. Each row in the collection triggers a before statement, before row, after row and after statement timing point. For bulk inserts, the statement level triggers only fire at the start and the end of the the whole bulk operation, rather than for each row of the collection. This can cause some confusion if you are relying on the timing points from row-by-row processing.