Introduction to Bulking Oracle Tips by Burleson Consulting

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

PL/SQL is made up of two types of code: procedural and SQL, each one processed by a different engine, see Figure 3.1.  There is an overhead associated with switching from procedural code to SQL and back again because of the switching between the PL/SQL and SQL engines.

 Figure 3.1: Overview of the PL/SQL engine.

In small operations these switches are barely perceivable. But they can become a problem during large looping operations where the delay is multiplied many times.  To prevent this problem, Oracle provides a mechanism that in a single operation binds DML statements to whole collections, reducing the number of context switches.  This mechanism is known as bulk binding and is the subject of this chapter.

• Although examples in this chapter are designed to run on the Oracle 10g database, scripts to support previous versions are given wherever possible.  All timings are calculated in hundredths of a second using the dbms_utility.get_time function.  If greater accuracy is needed, switching to TIMESTAMPs and INTERVALs is advised.

Populating Collections Using Bulk Operations

Bulk binds can improve performance when loading collections from queries.  The BULK COLLECT INTO construct binds the output of the query to the collection, resulting in less communication between the PL/SQL and SQL engines.  This reduction enhances performance and removes the need to extend and populate the collection one line at a time.  This method requires all variables listed in the INTO clause to be collections.

In Oracle8i a separate collection is necessary for every column bound to the SQL, which can make the code long winded and ugly.  From Oracle9i Release 2 onwards, this restriction has been removed allowing the use of record structures during bulk operations as long as there is no reference to individual columns of the collection.  This is very important since statements which must reference individual columns of the collection require multiple collections to enable efficient binding.

The examples in this section require the availability of the bulk_collect_test table, which is created using the create_bulk_collect_test.sql script listed below.

create_bulk_collect_test.sql

CREATE TABLE bulk_collect_test AS
SELECT owner,
       object_name,
       object_id
FROM   all_objects;

Once this test table is in place, the first of the bulk collect examples can be run.

Bulk Collect

The bulk_collect.sql script compares the relative performance of manually populating a collection to populating it via a bulk operation.

bulk_collect.sql

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;
/

The bulk_collect.sql script defines a collection with the same rowtype as the test table.  It then populates the collection by manually extending and setting the values of the rows.  Finally it populates the collection using a bulk operation.  The output from this script is shown below.

SQL> @bulk_collect.sql
Regular (61204 rows): 29
Bulk    (61204 rows): 11

PL/SQL procedure successfully completed.

The bulk operation takes less than half the time to populate the collection from the query.

The previous example used a record structure to perform the bulk operation, an option not available until Oracle 9i Release 2.  However, the bulk_collect_8i.sql script shows how the same operation can be coded for versions prior to that release.

bulk_collect_8i.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_owner_tab IS TABLE OF bulk_collect_test.owner%TYPE;
  TYPE t_object_name_tab IS TABLE OF bulk_collect_test.object_name%TYPE;
  TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;

  l_owner_tab        t_owner_tab       := t_owner_tab();
  l_object_name_tab  t_object_name_tab := t_object_name_tab();
  l_object_id_tab    t_object_id_tab   := t_object_id_tab();
  l_start            NUMBER;
BEGIN
  -- Time a regular population.
  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_id
                  FROM   bulk_collect_test)
  LOOP
    l_owner_tab.extend;
    l_object_name_tab.extend;
    l_object_id_tab.extend;

    l_owner_tab(l_owner_tab.last)             := cur_rec.owner;
    l_object_name_tab(l_object_name_tab.last) := cur_rec.object_name;
    l_object_id_tab(l_object_id_tab.last)     := cur_rec.object_id;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular (' || l_owner_tab.count || ' rows): ' ||
                       (DBMS_UTILITY.get_time - l_start));
 
  -- Time bulk population. 
  l_start := DBMS_UTILITY.get_time;

  SELECT owner,
         object_name,
         object_id
  BULK COLLECT INTO l_owner_tab,
                    l_object_name_tab,
                    l_object_id_tab
  FROM   bulk_collect_test;

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

Each column in the bulk operation is defined as a separate collection.  The results from this script are shown below.

SQL> @bulk_collect_8i.sql
Regular (61204 rows): 46
Bulk    (61204 rows): 14

PL/SQL procedure successfully completed.

Once again the bulk operation is quicker than the conventional approach.  The next section looks at how bulk operations can be used with explicit cursors.

Bulk Collect from an Explicit Cursor

For the majority of situations, a standard bulk collect will suffice. But in some circumstances, especially when limiting the collection volume, it may be necessary to perform a bulk collection from an explicit cursor.  The bulk_collect_from_cursor.sql script compares the manual population to the bulk population of a collection from an explicit cursor.

bulk_collect_from_cursor.sql

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;

  CURSOR c_data IS
    SELECT *
    FROM   bulk_collect_test;
BEGIN
  -- Time a regular population.
  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;
  LOOP
    l_tab.extend;

    FETCH c_data
    INTO  l_tab(l_tab.last);

    IF c_data%NOTFOUND THEN
      l_tab.delete(l_tab.last);
      EXIT;
    END IF;
  END LOOP;
  CLOSE c_data;

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

  -- Time bulk population. 
  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;
  FETCH c_data
  BULK COLLECT INTO l_tab;
  CLOSE c_data;

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

The code associated with the manual population looks rather clumsy, compared to the bulk collection.  The results from the bulk_collect_from_cursor.sql script show that the bulk operation is massively more efficient than the manual operation.  The performance difference is due to a combination of bulk processing and reduced amount of code required to complete the operation.

SQL> @bulk_collect_from_cursor.sql
Regular (61204 rows): 249
Bulk    (61204 rows): 12

PL/SQL procedure successfully completed.

The bulk_collect_from_cursor_8i.sql script shows how this operation is coded for previous versions of Oracle.

bulk_collect_from_cursor_8i.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_owner_tab IS TABLE OF bulk_collect_test.owner%TYPE;
  TYPE t_object_name_tab IS TABLE OF bulk_collect_test.object_name%TYPE;
  TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;

  l_owner_tab        t_owner_tab       := t_owner_tab();
  l_object_name_tab  t_object_name_tab := t_object_name_tab();
  l_object_id_tab    t_object_id_tab   := t_object_id_tab();
  l_start            NUMBER;

  CURSOR c_data IS
    SELECT owner,
           object_name,
           object_id
    FROM   bulk_collect_test;
BEGIN
  -- Time a regular population.
  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;
  LOOP
    l_owner_tab.extend;
    l_object_name_tab.extend;
    l_object_id_tab.extend;

    FETCH c_data
    INTO  l_owner_tab(l_owner_tab.last),
          l_object_name_tab(l_object_name_tab.last),
          l_object_id_tab(l_object_id_tab.last);

    IF c_data%NOTFOUND THEN
      l_owner_tab.delete(l_owner_tab.last);
      l_object_name_tab.delete(l_object_name_tab.last);
      l_object_id_tab.delete(l_object_id_tab.last);
      EXIT;
    END IF;
  END LOOP;
  CLOSE c_data;

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

  -- Time bulk population.
  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;
  FETCH c_data
  BULK COLLECT INTO l_owner_tab,
                    l_object_name_tab,
                    l_object_id_tab;
  CLOSE c_data;

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

The results from the bulk_collect_from_cursor_8i.sql script show a similar difference in performance speed.

SQL> @bulk_collect_from_cursor_8i.sql
Regular (61204 rows): 358
Bulk    (61204 rows): 15

PL/SQL procedure successfully completed.

How bulk collections can be split into more manageable chunks using the limit clause is shown in the next section.

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006. You can buy the book for only $19.95 (40%-off) when you buy directly from the publisher.

This is the BC Oracle DBA Scripts collection with Oracle DBA Scripts for tuning, monitoring, a professional download of over 600 Oracle DBA Scripts.