Total Pageviews

October 8, 2015

10/08/2015 10:39:00 AM
Bulk Collect Basics

The PL/SQL engine then continues processing until the next row is required, and the process repeats. 
A context switch is very fast, but if performed over and over again, the constant switching can take a noticeable amount of time. 
A bulk collect is a method of fetching data where the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. The SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine. 
All the rows are retrieved with only 2 context switches. The larger the number of rows processed, the more performance is gained by using a bulk collect
Remember that collections are held in memory, so doing a bulk collect from a large query could cause a considerable performance problem. In actual fact you would rarely do a straight bulk collect in this manner. Instead you would limit the rows returned using the LIMIT clause and move through the data processing smaller chunks. 
This gives you the benefits of bulk binds, without hogging all the server memory.

For that use :

    BULK COLLECT INTO l_tab LIMIT 10000;

From Oracle 10g onward, the optimizing PL/SQL compiler converts cursor FOR LOOPsinto BULK COLLECTs with an array size of 100
Related Posts Plugin for WordPress, Blogger...