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;