Anshuman Ghosh

This post is relevant in context of bulk operations we do in PLSQL (mostly as part of processes or batch jobs. A practical example is, having a cursor which returns a BIG collection of data, which then has to be processed row by row.

Without using Bulk Collect

The slow (read inefficient) way to do it is to read the cursor row by row, and process the data.

Example-

DECLARE
   my_sal emp.sal%TYPE;
   CURSOR c1 IS SELECT sal FROM emp WHERE job = my_job;
BEGIN
   ...
   OPEN c1; 
   LOOP
      FETCH c1 INTO my_sal;
      EXIT WHEN c1%NOTFOUND;
        bonus := sal * 0.15; 
        ... further processing (business logic)
        ...
        ...
   END LOOP;
END;

Using Bulk Collect

When using bulk collect, the complete cursor data is fetched into a collection in 1 shot, thereby saving of multiple reads from cursor (and the associated overhead time consumed in context switching between SQL fetches and PLSQL program process).

Example-

PROCEDURE process_all_rows
IS
   TYPE employees_collection 
   IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;
   l_employees employees_collection;
BEGIN
   SELECT *
   BULK COLLECT INTO l_employees
      FROM employees;
     
   FOR indx IN 1 .. l_employees.COUNT 
   LOOP
       my_function_call(l_employees(indx));
   END LOOP;
END process_all_rows;

Performance metrics

If you wish to have real time metrics of advantages of using bulk_collect, we can do so by timing the processes - once using normal cursor operation and once using bulk collect.

Add comment