Bulk Inserts with Oracle

The Old Fashioned Way

A quick glance at the following Code should make one point very clear: This is straightforward code; unfortunately, it takes a lot of time to run - it is "old-fashioned" code, so let's improve it using collections and bulk processing.

CREATE OR REPLACE PROCEDURE test_proc IS
BEGIN
  FOR x IN (SELECT * FROM all_objects)
  LOOP
    INSERT INTO t1
    (owner, object_name, subobject_name, object_id,
     data_object_id, object_type, created, last_ddl_time,
     timestamp, status, temporary, generated, secondary)
    VALUES
    (x.owner, x.object_name, x.subobject_name, x.object_id,
    x.data_object_id, x.object_type, x.created,
    x.last_ddl_time, x.timestamp, x.status, x.temporary,
    x.generated, x.secondary);
  END LOOP;
  COMMIT;
END test_proc;
/

CREATE TABLE t1 AS SELECT * FROM all_objects WHERE 1 = 2;

SQL> set timing on;
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.84
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.03
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.54

Very slow - do not use it in that way!

USING Bulk Collect

Converting to collections and bulk processing can increase the volume and complexity of your code. If you need a serious boost in performance, however, that increase is well-justified.

Collections, an evolution of PL/SQL tables that allows us to manipulate many variables at once, as a unit. Collections, coupled with two new features introduced with Oracle 8i, BULK_COLLECT and FORALL, can dramatically increase the performance of data manipulation code within PL/SQL.

CREATE OR REPLACE PROCEDURE test_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS SELECT * FROM all_objects;

BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO t1 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END test_proc;
/

SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.34
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.20
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.90

Eliminate CURSOR LOOP at all

You may eliminate the CURSOR Loop at all, the resulting Procedure is compacter and the performance is more or less the same.

CREATE OR REPLACE PROCEDURE test_proc
IS
TYPE TObjectTable IS TABLE OF ALL_OBJECTS%ROWTYPE;
ObjectTable$ TObjectTable;

BEGIN
   SELECT * BULK COLLECT INTO ObjectTable$
     FROM ALL_OBJECTS;

     FORALL x in ObjectTable$.First..ObjectTable$.Last
     INSERT INTO t1 VALUES ObjectTable$(x) ;
END;
/

SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.51
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.35
SQL> exec test_proc;

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.46