Bulk Collect In Oracle PL/SQL

By Jing Hong on October 27, 2010 | 1 Comment

Using Oracle BULK COLLECT technique enable the PL/SQL engine to collect many rows at once and place them in a collection of array.

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

You should have heard that BULK COLLECT together with FORALL can help a PL/SQL to perform better in terms of average execution time.

The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. FORALL does not require a LOOP command.

BULK COLLECT and FORALL statements can drastically improve the performance. For example, I have rewritten a PL/SQL procedure used in a batch job and in result the execution time has been reduced from 40 minutes to 30 seconds only by using BULK COLLECT and FORALL.

DECLARE 
 
-- Begin Cursor Definition
 
CURSOR bulk_table_select IS 
	SELECT TAB.object_id,
	       TAB.apps_id,
	       TAB.batch_id
	FROM table_select        TAB_SELECT;
 
TYPE object_id_tab IS TABLE OF table_select.object_id%TYPE INDEX BY BINARY_INTEGER;
TYPE apps_id_tab IS TABLE OF table_select.apps_id%TYPE INDEX BY BINARY_INTEGER;
TYPE batch_id_tab  IS TABLE OF table_select.batch_id%TYPE INDEX BY BINARY_INTEGER;
 
lt_object_id                 object_id_tab;
lt_apps_id            apps_id_tab;
lt_batch_id                  batch_id_tab;
 
BEGIN 
 
-- Begin Bulk Select & Delete
 
OPEN bulk_table_select;
 
LOOP
    FETCH bulk_table_select BULK COLLECT INTO lt_object_id,lt_apps_id,lt_batch_id
    LIMIT 5000;
    EXIT WHEN lt_batch_id.COUNT = 0;
    FORALL i IN lt_batch_id.FIRST..lt_batch_id.LAST
 
    DELETE FROM table_delete TAB_DELETE
    WHERE batch_id = lt_batch_id(i)
    AND  apps_id = lt_apps_id(i);
END LOOP;
 
CLOSE bulk_table_select;
 
commit;
 
END;