Bulk Collection of DML Results Oracle Tips by Burleson Consulting

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

The RETURNING clause is used to return specific columns from rows manipulated by DML statements.  When DML statements manipulate multiple rows, the data returned can be loaded into a collection using a bulk operation.  The returning_bulk_collect.sql script provides an example of this functionality.

returning_bulk_collect.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;

  l_tab  t_object_id_tab;
BEGIN
  DELETE FROM bulk_collect_test
  RETURNING object_id BULK COLLECT INTO l_tab;

  DBMS_OUTPUT.put_line(‘Deleted IDs : ' || l_tab.count || ' rows');

  ROLLBACK;
END;
/

The returning_bulk_collect.sql script defines a collection, deletes the data from the test table and returns the OBJECT_IDs of the deleted rows.  To preserve the data, the script performs a rollback, allowing for multiple runs.  The output from this script shows that the collection is populated as expected.

SQL> @returning_bulk_collect.sql
Deleted IDs : 61202 rows

The FORALL command can be used in conjunction with a DML statement with a RETURNING clause.  In this situation, both the DML and the return data are bulk operations.  The forall_returning_bulk_collect.sql script provides an example of this functionality.

forall_returning_bulk_collect.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;

  l_in_tab   t_object_id_tab;
  l_out_tab  t_object_id_tab;
BEGIN
  -- Populate collection use in forall.
  SELECT object_id
  BULK COLLECT INTO l_in_tab
  FROM   bulk_collect_test
  WHERE  rownum < 101; 

  FORALL i IN l_in_tab.first .. l_in_tab.last
    DELETE FROM bulk_collect_test
    WHERE  object_id = l_in_tab(i)
    RETURNING object_id BULK COLLECT INTO l_out_tab;

  DBMS_OUTPUT.put_line('Starting IDs : ' || l_in_tab.count || ' rows');
  DBMS_OUTPUT.put_line('Deleted IDs  : ' || l_out_tab.count || ' rows');

  ROLLBACK;
END;
/

The forall_returning_bulk_collect.sql script defines two collections, one to support the FORALL statement and one to support the RETURNING clause.  The first collection is populated to provide IDs of rows to be deleted. Next the bulk delete is performed and the data returned into the second collection.  Then the size of both collections is reported.  The output from this script is displayed below.

SQL> @forall_returning_bulk_collect.sql
Starting IDs : 100 rows
Deleted IDs  : 100 rows

PL/SQL procedure successfully completed.

As expected, the contents of the input and output collections are identical.  The next section investigates the use of bulk DML operations using the FORALL statement.

FORALL in PL/SQL

The FORALL statement allows insert, update and delete statements to be bound to collections in a single operation, resulting in less communication between the PL/SQL and SQL engines.  As with the BULK COLLECT option, this reduction in context switches between the two engines results in better performance.

The following examples show how bulk DML operations are coded and the performance gains when using them.  The examples are based around the FORALL_TEST table which can be created using the create_forall_test.sql script listed below.

create_forall_test.sql
CREATE TABLE forall_test (
  id           NUMBER(10),
  code         VARCHAR2(10),
  description  VARCHAR2(50));

ALTER TABLE forall_test ADD (
  CONSTRAINT forall_test_pk PRIMARY KEY (id));

ALTER TABLE forall_test ADD (
  CONSTRAINT forall_test_uk UNIQUE (code));

First compare the performance of conventional and bulk insert operations.

Bulk INSERT Operations

With the forall_test table created, the performance of individual inserts is compared against bulk inserts using the insert_forall.sql script listed below. 

The VALUES clause of the bulk operation does not reference the individual elements of the collection.  This syntax was introduced in Oracle9i Release 2 to allow bulk operations to use record structures.

insert_forall.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

  l_tab    t_forall_test_tab := t_forall_test_tab();
  l_start  NUMBER;
  l_size   NUMBER            := 10000;
BEGIN
  -- Populate collection.
  FOR i IN 1 .. l_size LOOP
    l_tab.extend;

    l_tab(l_tab.last).id          := i;
    l_tab(l_tab.last).code        := TO_CHAR(i);
    l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
  END LOOP;

  EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;

  -- Time regular inserts.

  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_tab.first .. l_tab.last LOOP
    INSERT INTO forall_test (id, code, description)
    VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
  END LOOP;

  DBMS_OUTPUT.put_line('Normal Inserts: ' ||
                       (DBMS_UTILITY.get_time - l_start)); 

  EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;

  -- Time bulk inserts. 

  l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO forall_test VALUES l_tab(i);

  DBMS_OUTPUT.put_line('Bulk Inserts  : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  COMMIT;
END;
/

The insert_forall.sql script is split into four main sections:

• Declaration - A table type is defined with the same structure as the test table, which is then used to declare and initialize a collection.  The collection is initialized to support the manual operation, not the bulk operation as these implicitly initialize collections.  In addition variables are defined to store the start time of each operation and the size of the collection.

• Populate Collection – Extends and populates the collection to the desired size.

• Time Regular Inserts – Loops through the collection performing a separate insert for each row and displays the time taken to complete the loop.

• Time Bulk Inserts – Performs a bulk operation to bind the collection into an insert statement and displays the time taken to complete the operation.

The types of results expected from the script are shown below, in this case, using a collection of 10,000 records.  The times will vary between servers and individual runs depending on the specification and usage levels of the servers.  The thing to focus on is not the absolute speed, but the comparison between the two.

SQL> @insert_forall.sql
Normal Inserts: 300
Bulk Inserts  : 19

PL/SQL procedure successfully completed.

The output shows it takes approximately 3 seconds to insert 10,000 rows via a conventional loop, while the bulk operation completes that same amount of work in approximately 0.2 seconds.  This is a dramatic illustration of the benefits of bulk operations.

For the sake of completeness, the insert_forall_8i.sql script shows how this operation would be coded prior to Oracle9i Release 2.  Notice that a separate collection is defined for each column referenced in the bind operation.

insert_forall_8i.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_id_tab IS TABLE OF forall_test.id%TYPE;
  TYPE t_code_tab IS TABLE OF forall_test.code%TYPE;
  TYPE t_desc_tab IS TABLE OF forall_test.description%TYPE;

  l_id_tab    t_id_tab   := t_id_tab();
  l_code_tab  t_code_tab := t_code_tab();
  l_desc_tab  t_desc_tab := t_desc_tab();
  l_start     NUMBER;
  l_size      NUMBER     := 10000;
BEGIN
  -- Populate collection.
  FOR i IN 1 .. l_size LOOP
    l_id_tab.extend;
    l_code_tab.extend;
    l_desc_tab.extend;

    l_id_tab(l_id_tab.last)     := i;
    l_code_tab(l_id_tab.last)   := TO_CHAR(i);
    l_desc_tab(l_desc_tab.last) := 'Description: ' || TO_CHAR(i);
  END LOOP;

  EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;

  -- Time regular inserts.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_id_tab.first .. l_id_tab.last LOOP
    INSERT INTO forall_test (id, code, description)
    VALUES (l_id_tab(i), l_code_tab(i), l_desc_tab(i));
  END LOOP;

  DBMS_OUTPUT.put_line('Normal Inserts: ' ||
                       (DBMS_UTILITY.get_time - l_start)); 

  EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;

  -- Time bulk inserts. 

  l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_id_tab.first .. l_id_tab.last
    INSERT INTO forall_test (id, code, description)
    VALUES (l_id_tab(i), l_code_tab(i), l_desc_tab(i));

  DBMS_OUTPUT.put_line('Bulk Inserts  : ' ||
                       (DBMS_UTILITY.get_time - l_start));
  COMMIT;
END;
/

The same performance improvements are also evident using this method.

SQL> @insert_forall_8i.sql
Normal Inserts: 274
Bulk Inserts  : 24

PL/SQL procedure successfully completed.

The next section compares the performance of conventional and bulk update operations.

Comments:

Depending on the release of Oracle BULK DMLs might not be supported across dblinks. According to Oracle, bulk collects over links are supported as of Oracle 9i.

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006. You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts: