Optimizing SQL Code - Oracle Database 11gR2 Performance Tuning - page 178

These arrays are used to BULK COLLECT the data from the SALES table and then to insert the
data in the MY_SALES table using a FORALL instruction. A common mistake is to think that
the FORALL statement is a loop—this statement is executed only once, for passing the arrays
as arguments to the INSERT .
We can see the results of this example in the following screenshot:
We can see a huge improvement in performance using the latter approach, instead of the
cursor-for-loop used in the first example, because the latter approach is more than 10 orders
of magnitude faster.
In the last example, all of the work is done in a single INSERT statement, selecting from the
SALES table to insert directly into the MY_SALES table. We use the hint /*+ APPEND */ (see
Exploring the optimizer hints in Chapter 7 , Improving the Oracle Optimizer for more details
about hints) to instruct the database to load the data in the table using a direct path load,
that is, bypassing the buffer cache and writing directly to the disk.