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
For that use :
From Oracle 10g onward, the optimizing PL/SQL compiler converts cursor
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