Bulk Collect

Oracle Bulk Collect is recommended to use for handling large number of rows. Note that, the larger the number of rows you will collect, the more performance improvement you will achieve.

For better understanding take a look at the examples bellow.

1. Bulk Collect can be coded without CURSOR loop, but only for small number of records.

CREATE OR REPLACE PROCEDURE bulk_collect_proc
IS
TYPE Table_Type IS TABLE OF books_table%ROWTYPE;
c_BooksTable Table_Type;
BEGIN
   SELECT * BULK COLLECT INTO c_BooksTable 
     FROM books_table;

     FORALL c IN c_BooksTable .FIRST..c_BooksTable .LAST
     INSERT INTO books_table_2 VALUES c_BooksTable(c) ;
END bulk_collect_proc;

To improve performance of upper example you should use APPEND_VALUE hint in INSERT statement.

 INSERT /*+ APPEND_VALUES */ INTO books_table_2 VALUES c_BooksTable(c) ;

2. For BULK COLLECT performance optimization and best practice to processing big amount of records in tables you should use also a LIMIT clause to reduce amount of memory used.

PROCEDURE bulk_collect_tuning
IS
    CURSOR cars_cur 
    IS 
        SELECT * 
          FROM sold_cars;

    TYPE cars_table IS TABLE OF cars_cur%ROWTYPE
        INDEX BY PLS_INTEGER;

    l_cars cars_table;
BEGIN   
    OPEN cars_cur;

        LOOP
            FETCH cars_cur 
                BULK COLLECT INTO l_cars LIMIT 1000;

                FOR row_num IN 1 .. l_cars.COUNT 
                LOOP
                     --use records l_cars(row_num)

                END LOOP;

            EXIT WHEN l_cars.COUNT = 0;

       END LOOP;

   CLOSE cars_cur;

END bulk_collect_tuning;

3. A little bit complex example of using Bulk Collect.

Example:

PROCEDURE cost_action
IS
--declare cursor for source data
CURSOR c IS
SELECT null                 CALL_REFERENCE,
       local_inc_time       CALL_REFERENCE_TIME,
       null                 DURATION,
       null                 DATA_UPLINK,
       null                 DATA_DOWNLINK,
       oaddr                CALLING_MSISDN,
       daddr                CALLED_MSISDN,
       null                 CALLING_IMSI,
       null                 CALLING_IMEI,
       null                 LAC,
       null                 CELL,
       null                 COST,
       null                 SERVICE_TYPE,
       orig_c               ORIGINATING_NETWORK,
       dest_c               TERMINATING_NETWORK,
       null                 CALL_TYPE,
       null                 RECORD_TYPE,
       id                   ID,
       sysdate              INSERT_DATE,
       null                 CHARGING_MSISDN,
       null                 CC_ID,
       null                 ACCOUNT_OBJ_ID0,
       oaddr                ANUM,
       daddr                BNUM,
       get_category(oaddr,daddr,oiwtype, diwtype,local_incoming_time) SERVICE_NAME,
       null                 CNUM
  FROM cdrs
 WHERE success_indicator = 8
   AND incoming_time >= '20100901000000'
   AND status= 0;

TYPE Tcc_record IS
  TABLE OF c%ROWTYPE
  INDEX BY BINARY_INTEGER;

cc_records      Tcc_record;

--declare type for ids to update(like imported)
TYPE Tids_to_update IS
  TABLE OF NUMBER
  INDEX BY BINARY_INTEGER;

ids_to_update   Tids_to_update;

CURSOR d IS
 SELECT c.call_reference, c.call_reference_time, c.duration,
        c.data_uplink, c.data_downlink, c.calling_msisdn,
        c.called_msisdn, c.calling_imsi, c.calling_imei,
        c.lac, c.cell, c.cost,
        c.service_type, c.originating_network,
        c.terminating_network,
        c.call_type, c.record_type, c.id,
        c.insert_date, c.charging_msisdn, c.cc_id,
        c.account_obj_id0, c.anum, c.bnum,
        c.service_name, c.cnum
   FROM cost_action c;

TYPE cc_record
IS
TABLE OF d%ROWTYPE
INDEX BY BINARY_INTEGER;

mm_records      cc_record;

v_counter   number := 0;
BEGIN
log_entry ('COST ACTION started');

OPEN c;
LOOP
FETCH c BULK COLLECT INTO cc_records LIMIT 20000;

v_counter := v_counter + cc_records.COUNT;

   FOR i IN cc_records.FIRST .. cc_records.LAST
   LOOP
     ids_to_update(i) := cc_records(i).id;
   END LOOP;

   FORALL i IN 1..cc_records.COUNT
   INSERT INTO cost_action_values cc_records(i);

   FORALL i IN 1..cc_records.COUNT
   UPDATE   cdrs
      SET   status = 1
    WHERE   id = ids_to_update(i);

COMMIT;

EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;

--update
UPDATE cost_action SET charging_msisdn = anum
 WHERE service_name NOT LIKE 'TERM%'
   AND service_name IS NOT NULL
   AND charging_msisdn IS NULL;

UPDATE cost_action SET charging_msisdn = bnum
 WHERE service_name LIKE 'TERM%'
   AND charging_msisdn IS NULL;

COMMIT;

--insert records in table cost_action_all
OPEN d;
LOOP
  FETCH d BULK COLLECT INTO mm_records LIMIT 20000;

  FORALL i IN 1..mm_records.COUNT
  INSERT INTO cost_actiom_all VALUES mm_records(i);

COMMIT;

EXIT WHEN d%NOTFOUND;
END LOOP;
CLOSE d;

COMMIT;

log_entry('COST ACTION  complete. '||v_counter||' records transfered.');

EXCEPTION
 WHEN OTHERS THEN
  log_entry ('COST ACTION failed: ' || SQLERRM);
  log_entry ('COST ACTION failed: ' || SQLCODE);
END cost_action;

TOOLS

POSTS