Oracle Database 11gR2 Performance Tuning Cookbook

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