Oracle Bulk Collect
One method of fetching data is an Oracle bulk collect. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine. When rows are retrieved using Oracle bulk collect, they are retrieved with only 2 context switches. The larger the number of rows you would like to collect with Oracle bulk collect, the more performance improvement you will see using an Oracle bulk collect.
Starting in Oracle10g, an Oracle bulk collect may be performed by the the PL/SQL engine for you. The PL/SQL engine may automatically use Oracle bulk collect to collect 100 rows at a time because of a cursor loop. This use of Oracle bulk collect allows your code to process rows without having to setup and execute the Oracle bulk collect operation. The result of this use of Oracle bulk collect is that bulk collecting 75 rows may not provide you with much of a benefit, but using Oracle bulk collect to collect large numbers of rows (many hundreds) will provid increased performance.
dfOracle Bulk collect is easy to use. First, define the collection or collections that will be collected using the Oracle bulk collect. Next, define the cursor to retrieve the data in the Oracle bulk collect. Finally, bulk collect the data into the collections.
A simple Oracle bulk collect example is shown below:
