Bulk Binding: BULK COLLECT

Use BULK COLLECT to retrieve large volume of data by using a single operation to bind the DML statements to whole collections, reducing the number of context switches, and hence greatly increases the performance.

In my experience in reading an XML file with more than 20,000 records, it took more than an hour to parse the data using an ordinary FOR loop.  However, after using the BULK COLLECT, it is greatly reduced to less than 2 minutes.

create or replace PROCEDURE SP_BULK_COLLECT AS /********************************************************************* NAME: SP_BULK_COLLECT PURPOSE: Example for fetching the record in large volume. *********************************************************************/ CURSOR c_products IS SELECT PRODUCT_CD FROM TBL_PRODUCT ORDER BY PRODUCT_CD; TYPE lt_products IS TABLE OF c_products%ROWTYPE INDEX BY BINARY_INTEGER; la_products lt_products; BEGIN OPEN c_products; LOOP FETCH c_products BULK COLLECT INTO la_products LIMIT 100; FOR i IN 1..la_products.COUNT LOOP dbms_output.put_line(la_products(i).PRODUCT_CD); END LOOP; EXIT WHEN c_products%NOTFOUND; END LOOP; CLOSE c_products; END;

References

1. Introduction to Bulking, http://www.dba-oracle.com/plsql/t_plsql_bulking.htm