ORACLE BULK COLLECT

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: