Databases Reference
In-Depth Information
In step 9, we use a slightly modified version of the script, thanks to the
LIMIT
CLAUSE
of the
BULK
COLLECT
statement. We divide the job in batches of 200 records each; for every batch
of records we use the same
BULK
COLLECT
and
FORALL
statements used in step 7.
In the following screenshot, we can see the output of this execution:
In step 10, we drop the table and custom datatypes used for this recipe.
There's more...
We are aware that
BULK
COLLECT
and
FORALL
statements complete the process faster
than the
one row at a time
approach of the
FOR
statement in step 3.
Using the
LIMIT
clause, we can see a slight decline in performance, so why use it?
The reason is to conserve memory—when we
BULK
COLLECT
a large amount of data in
our user-defined datatype arrays, it is storing them in memory, using a resource that is
also being used for other purposes (sorts, joins, buffers, and so on). Limiting the number
of rows retrieved in every single batch execution, allows us to see a performance benefit
using array processing, without impacting the need for memory for other processes.
