PL/SQL - Bulk Collect

Bulk collect is one of my favourite features in PL/SQL programming. It gains significance when you require better performing code using collections to collect very large number of rows. larger the number of rows to be collected better is the performance using bulk collect. During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection before it switches back to the PL/SQL engine.  When rows are retrieved using Oracle bulk collect, they are retrieved with only two context switches.

HOW TO USE PLSQL BULK COLLECT

You can use the BULK COLLECT clause with a SELECT INTO or FETCH statement to retrieve a set of rows into a collection.

Lets see some of the use cases for bulk collect

1. BULK COLLECT INTO RECORD

TABLE BASED RECORD

DECLARE
   TYPE EmployeeSet IS TABLE OF employees%ROWTYPE;
   overpaid EmployeeSet;
     -- Holds set of rows from EMPLOYEES table.
 
BEGIN

   SELECT * BULK COLLECT INTO overpaid FROM employees
      WHERE salary >  5000 ORDER BY salary DESC;
-- Process data by examining collection or passing it to

   DBMS_OUTPUT.PUT_LINE
     (overpaid.COUNT || ' people more than 5000.');
   FOR i IN overpaid.FIRST .. overpaid.LAST
   LOOP
     DBMS_OUTPUT.PUT_LINE
       (overpaid(i).first_name || ' makes ' || overpaid(i).salary);
   END LOOP;
END;
-------------------------------------------------------------------
USER DEFINED RECORD

DECLARE
  TYPE rec_employees IS RECORD(
    
    name   VARCHAR2(100),
    salary NUMBER);

  TYPE EmployeeSet IS TABLE OF rec_employees;
  overpaid EmployeeSet;

BEGIN
  -- With one query,
  -- bring all relevant data into collection of records.
  SELECT FIRST_NAME, SALARY
    BULK COLLECT
    INTO overpaid
    FROM employees
   WHERE salary > 5000
   ORDER BY salary DESC;
  -- Process data by examining collection or passing it to

  DBMS_OUTPUT.PUT_LINE(overpaid.COUNT || ' people make less than 5000.');
  FOR i IN overpaid.FIRST .. overpaid.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(overpaid(i)
                         .NAME || ' makes ' || overpaid(i).salary);
  END LOOP;
END;

USING FETCH STATEMENT

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   TYPE NumList IS TABLE OF EMPLOYEES%ROWTYPE;
   emp_cv EmpCurTyp;
   emp NumList;
 
BEGIN
  OPEN emp_cv FOR 'SELECT * FROM employees WHERE SALARY > 5000';
   FETCH emp_cv BULK COLLECT INTO emp;
   FOR i in 1..emp.count loop
     DBMS_OUTPUT.PUT_LINE(emp(i).FIRST_NAME || ' ' || emp(i).salary);
     END LOOP;
 END;
 

LIMIT CLAUSE

When you use BULK COLLECT, you retrieve multiple rows with every fetch statement, reducing context switching between the SQL and PL/SQL engines, and thereby reducing a significant time in the processing. However collection is stored in PGA so a lot of memory is consumed, To overcome this oracle has a Limit clause. The example below shows the same code with a limit clause.

DECLARE
  c_limit CONSTANT PLS_INTEGER DEFAULT 100;
  TYPE EmpCurTyp IS REF CURSOR;
  TYPE NumList IS TABLE OF EMPLOYEES%ROWTYPE;
  emp_cv EmpCurTyp;
  emp    NumList;

BEGIN
  OPEN emp_cv FOR 'SELECT * FROM employees order by first_name';
  LOOP
    FETCH emp_cv BULK COLLECT
      INTO emp limit c_limit;
    EXIT WHEN emp.COUNT = 0;
  
    FOR i in 1 .. emp.count loop
      DBMS_OUTPUT.PUT_LINE(emp(i).FIRST_NAME || ' ' || emp(i).salary);
      DBMS_OUTPUT.put_line('Retrieved ' || emp.COUNT);
    END LOOP;
  END LOOP;
END;
-----------------------------------------------------------

Now, no matter how many rows are in the employees table, my session only uses the memory required for 100 rows. In traditional oracle version the then new pl/sql optimizer converted the for loop into a c code to get 100 rows every fetch , considering that the amount of memory for most of the tables needed for 100 rows wont cost that significant memory outage. It's a good practice to follow that limit altough i have never witnessed a limit as low as 100 in any of the projects i have worked in and for that matter increasing this limit to 500 or 1000 does not really seem to offer any performance degradation.

Choose only those which is required

lAnother thing which we should consider in bulk collect is we should not put everything in the collection like if i require only the first_name and salary in output there is no point to load everything into my collection, This could degrade the performance in bulk collection.

Next read For All.

#PLSQL #ORACLE #ORACLEDEVELOPER #PLSQLDEVELOPER #DATABASE #ORACLE18C #BULKCOLLECT