Collection Bulk Bind - Bulk Collect, For All

Bulk Binds

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.

First we create a test table.

CREATE TABLE test1(
  id           NUMBER(10),
  description  VARCHAR2(50));
  
ALTER TABLE test1 ADD (
  CONSTRAINT test1_pk PRIMARY KEY (id));
  
SET TIMING ON

The time taken to insert, update then delete 10,000 rows using regular FOR..LOOP statements is approximately 34 seconds on my test server.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
  
  FOR i IN t_id.first .. t_id.last LOOP
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));
  END LOOP;
    
  FOR i IN t_id.first .. t_id.last LOOP
    UPDATE test1
    SET    description = t_description(i)
    WHERE  id = t_id(i);
  END LOOP;
    
  FOR i IN t_id.first .. t_id.last LOOP
    DELETE test1
    WHERE  id = t_id(i);
  END LOOP;
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.00

Using the FORALL construct to bulk bind the inserts this time is reduced to 18 seconds.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
  
  FORALL i IN t_id.first .. t_id.last
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));
    
  FORALL i IN t_id.first .. t_id.last
    UPDATE test1
    SET    description = t_description(i)
    WHERE  id = t_id(i);
    
  FORALL i IN t_id.first .. t_id.last
    DELETE test1
    WHERE  id = t_id(i);
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.05

A collection must be defined for every column bound to the DML which can make the code rather long winded, but the performance improvements more than make up for this.

Bulk binds can also improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. To show this we must first load our table with some data.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
  
  FORALL i IN t_id.first .. t_id.last
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));

  COMMIT;
END;
/

Populating two collections with 10,000 rows using a FOR..LOOP takes approximately 1.02 seconds.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
  
  CURSOR c_data IS
    SELECT *
    FROM   test1;
BEGIN
  FOR cur_rec IN c_data LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := cur_rec.id;
    t_description(t_description.last) := cur_rec.description;
  END LOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.02

Using the BULK COLLECT INTO construct reduces this time to approximately 0.01 seconds.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type;
  t_description  description_type;
BEGIN
  SELECT id, description 
  BULK COLLECT INTO t_id, t_description FROM test1;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01