Array processing and bulk-collect

In this recipe, we will see how to use the BULK COLLECT and FORALL statements to speed up the processing of huge amounts of data in a single statement.

We will also see how to limit the amount of memory used for these statements, to avoid a decrease in performance due to reduced available memory for other processes.

How to do it...

The following steps will demonstrate array processing:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create a MY_CUSTOMERS table to store the ID and FIRST_NAME of the customers:

   CREATE TABLE sh.MY_CUSTOMERS (
     CUST_ID NUMBER,
     CUST_FIRST_NAME VARCHAR2(20));
   

3. Populate the MY_CUSTOMERS table using an INSERT statement inside a FOR loop:

   SET TIMING ON
   BEGIN
     FOR aRow IN (SELECT CUST_ID, CUST_FIRST_NAME FROM CUSTOMERS)
     LOOP
       INSERT INTO sh.MY_CUSTOMERS (CUST_ID, CUST_FIRST_NAME)
         VALUES (aRow.CUST_ID, aRow.CUST_FIRST_NAME);
     END LOOP;
   END;
   /
   SET TIMING OFF
   

4. Truncate the MY_CUSTOMERS table to empty it:

   TRUNCATE TABLE sh.MY_CUSTOMERS...