Logo Background

Bulk Collect In Oracle PL/SQL

  • By 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;
    Previous
    Next
    » Unix Filename Pattern Matching
  1. #1 Kumar
    October 29, 2010 5:37 am

    Hi Guru,

    Do you have any sample program for the above program.

    Post ReplyPost Reply
Leave a Comment