Name

SQL-18: Use FORALL to improve performance of collection-based DML (Oracle8i).

Synopsis

Recognizing that you often need to modify (insert, delete, or update) large numbers of rows in the database from within PL/SQL, Oracle8i offers a new FORALL statement. This statement can dramatically improve your DML performance by reducing the number of “context switches” between the PL/SQL statement executor and the SQL engine.

Consider using FORALL whenever you perform a DML operation within a loop.

Example

The following cursor FOR loop performs many individual row updates:

BEGIN
   FOR book_rec IN book_pkg.book_cur
   LOOP
      UPDATE borrowings 
         SET borrow_date = SYSDATE
             borrower_id = book_rec.user_id
       WHERE isbn = book_rec.isbn;
   END LOOP;
END;

Now I use FORALL to accomplish the same thing, but with a single pass to the SQL engine and RDBMS:

DECLARE
   TYPE books_t IS TABLE OF borrower.user_id%TYPE;
   books books_t := books_t(  );
   TYPE isbns_t IS TABLE OF book.isbn%TYPE;
   isbns isbns _t := isbns_t(  );
BEGIN
   FOR book_rec IN book_pkg.book_cur
   LOOP
      books.EXTEND;
      isbns.EXTEND;
      books(books.LAST) := book_rec.user_id;
      isbns(isbns.LAST) := book_rec.isbn;
   END LOOP;

   FORALL indx IN books.FIRST .. books.LAST
      UPDATE borrowings 
         SET borrow_date = SYSDATE
             borrower_id = books(indx)
       WHERE isbn = isbns(indx);
END;

Notice that I still need the cursor FOR loop to populate my collections. The time it takes to do this, however, is usually more than offset by the improvements in UPDATE processing.

Benefits

You can significantly improve ...

Get Oracle PL/SQL Best Practices now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.