Pages

Thursday, November 12, 2015

Bulk Collect In Oracle PL/SQL

Using
Oracle BULK COLLECT technique enables the PL/SQL engine to collect many rows at
once and place them in a collection of array.

A
collection is an ordered group of elements, all of the same type. It is a
general concept that encompasses lists, arrays, and other familiar datatypes.
Each element has a unique subscript that determines its position in the
collection.

You
should have heard that BULK COLLECT together with FORALL can help a PL/SQL to
perform better in terms of average execution time.

The
keyword FORALL instructs the PL/SQL engine to bulk-bind input collections
before sending them to the SQL engine. FORALL does not require a LOOP command.

BULK
COLLECT and FORALL statements can drastically improve the performance. For
example, I have rewritten a PL/SQL procedure used in a batch job and in result
the execution time has been reduced from 40 minutes to 30 seconds only by using
BULK COLLECT and FORALL.