Bulk Collect

Oracle Bulk Collect is recommended to use for handling large number of rows. Note that, the larger the number of rows you will collect, the more performance improvement you will achieve.

For better understanding take a look at the examples bellow.

1. Bulk Collect can be coded without CURSOR loop, but only for small number of records.

To improve performance of upper example you should use APPEND_VALUE hint in INSERT statement.

2. For BULK COLLECT performance optimization and best practice to processing big amount of records in tables  you should use also a LIMIT clause to reduce amount of memory used.

3. A little bit complex example of using Bulk Collect.

Example: