ORACLE BULK COLLECT

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 queries. The BULK COLLECT INTO construct binds the output of the query to the collection. To test this, create the following table.

CREATE TABLE bulk_collect_test AS

SELECT owner,

       object_name,

       object_id

FROM   all_objects;

The following code compares the time taken to populate a collection manually and using a bulk bind.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();

  l_start  NUMBER;

BEGIN

  -- Time a regular population.

  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT * FROM   bulk_collect_test)

  LOOP

   l_tab.extend;

   l_tab(l_tab.last) := cur_rec;

  END LOOP;

   DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' || (DBMS_UTILITY.get_time - l_start));

   -- Time bulk population.

  l_start := DBMS_UTILITY.get_time;

  SELECT * BULK COLLECT INTO l_tab FROM   bulk_collect_test;

  DBMS_OUTPUT.put_line('Bulk    (' || l_tab.count || ' rows): ' || (DBMS_UTILITY.get_time - l_start));

END;

/

Regular (42578 rows): 66

Bulk    (42578 rows): 4

PL/SQL procedure successfully completed.

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

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

Remember that collections are held in memory, so doing a bulk collect from a large query could cause a considerable performance problem. In actual fact you would rarely do a straight bulk collect in this manner. Instead you would limit the rows returned using the LIMIT clause and move through the data processing smaller chunks.