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;

CREATE OR REPLACE PROCEDURE bulk_collect_proc

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

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

PROCEDURE bulk_collect_tuning

CURSOR cars_cur

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 &gt;= '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;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

PROCEDURE cost_action

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

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