Oracle and PL/SQL Recipes: A Problem-Solution Approach

13-3. Speeding Up Read/Write Loops

Problem

You have identified a loop that reads and writes large batches of data. You want to speed it up.

Solution

Use a BULK COLLECT statement to fetch the target data records, and then use a FORALL loop to update the local database. For example, suppose you want to speed up the sync_hr_data procedure demonstrated in Chapter 11:

CREATE OR REPLACE PROCEDURE sync_hr_data AS CURSOR    driver IS SELECT    * FROM      employees@hr_data; TYPE    recs_type IS TABLE OF driver%ROWTYPE INDEX BY BINARY_INTEGER; recs    recs_type; BEGIN    OPEN driver;    FETCH driver BULK COLLECT INTO recs;    CLOSE driver;    FORALL i IN 1..recs.COUNT       UPDATE    employees       SET    first_name    = recs(i).first_name       WHERE    employee_id ...