Bulk Binds and Record Processing in Oracle9i Release 2

This article is an update of one written for Oracle8i (Bulk Binds) which includes new features available in Oracle9i Release 2.

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 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.

In Oracle8i a collection must be defined for every column bound to the DML which can make the code rather long winded. Oracle9i allows us to use Record structures during bulk operations so long as we don't reference individual columns of the collection. This restriction means that updates and deletes which have to reference inividual columns of the collection in the where clause are still restricted to the collection-per-column approach used in Oracle8i.

To test bulk binds using records we first 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 10,000 rows using regular FOR..LOOP statements is approximately 9 seconds on my test server:

TRUNCATE TABLE test1;
DECLARE
  TYPE test1_tab IS TABLE OF test1%ROWTYPE;

  t_tab  test1_tab := test1_tab();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_tab.extend;

    t_tab(t_tab.last).id          := i;
    t_tab(t_tab.last).description := 'Description: ' || To_Char(i);
  END LOOP;

  FOR i IN t_tab.first .. t_tab.last LOOP
    INSERT INTO test1 (id, description)
    VALUES (t_tab(i).id, t_tab(i).description);
  END LOOP;

  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.03

Using the FORALL construct to bulk bind the inserts this time is reduced to less than 1/10 of a second:

TRUNCATE TABLE test1;
DECLARE
  TYPE test1_tab IS TABLE OF test1%ROWTYPE;

  t_tab  test1_tab := test1_tab();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_tab.extend;

    t_tab(t_tab.last).id          := i;
    t_tab(t_tab.last).description := 'Description: ' || To_Char(i);
  END LOOP;

  FORALL i IN t_tab.first .. t_tab.last
    INSERT INTO test1 VALUES t_tab(i);

  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

Since no columns are specified in the insert statement the record structure of the collection must match the table exactly.

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. Populating two collections with 10,000 rows using a FOR..LOOP takes approximately 0.05 seconds:

DECLARE
  TYPE test1_tab IS TABLE OF test1%ROWTYPE;

  t_tab  test1_tab := test1_tab();

  CURSOR c_data IS
    SELECT *
    FROM   test1;
BEGIN
  FOR cur_rec IN c_data LOOP
    t_tab.extend;

    t_tab(t_tab.last).id          := cur_rec.id;
    t_tab(t_tab.last).description := cur_rec.description;
  END LOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05

Using the BULK COLLECT INTO construct reduces this time to less than 0.01 seconds:

DECLARE
  TYPE test1_tab IS TABLE OF test1%ROWTYPE;

  t_tab  test1_tab := test1_tab();
BEGIN
  SELECT id, description
  BULK COLLECT INTO t_tab
  FROM test1;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

The select list must match the collections record definition exactly for this to be successful.

Oracle9i Release 2 also allows updates using record definitions by using the ROW keyword:

DECLARE
  TYPE test1_tab IS TABLE OF test1%ROWTYPE;

  t_tab  test1_tab := test1_tab();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_tab.extend;

    t_tab(t_tab.last).id          := i;
    t_tab(t_tab.last).description := 'Description: ' || To_Char(i);
  END LOOP;

  FOR i IN t_tab.first .. t_tab.last LOOP
    UPDATE test1
    SET    ROW = t_tab(i)
    WHERE  id  = t_tab(i).id;
  END LOOP;
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.08

The reference to the ID column within the WHERE clause means that the this statement cannot use a bulk bind directly. In order to use a bulk bind a separate collection must be defined for the id column:

DECLARE
  TYPE id_tab IS TABLE OF test1.id%TYPE;
  TYPE test1_tab IS TABLE OF test1%ROWTYPE;

  t_id   id_tab    := id_tab();
  t_tab  test1_tab := test1_tab();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_tab.extend;

    t_id(t_id.last)               := i;
    t_tab(t_tab.last).id          := i;
    t_tab(t_tab.last).description := 'Description: ' || To_Char(i);
  END LOOP;

  FORALL i IN t_tab.first .. t_tab.last
    UPDATE test1
    SET    ROW = t_tab(i)
    WHERE  id  = t_id(i);
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.01

For further information see:

Hope this helps. Regards Tim...

Back to the Top.