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.
-- Begin Cursor Definition
CURSOR bulk_table_select IS
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;
-- Begin Bulk Select & Delete
FETCH bulk_table_select BULK COLLECT INTO lt_object_id,lt_apps_id,lt_batch_id
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);
Next» Unix Filename Pattern Matching