Bulk Processing in PL/SQL: Elevating Performance with FORALL and BULK COLLECT

Bulk Processing in PL/SQL: Elevating Performance with FORALL and BULK COLLECT

When working with hundreds of thousands or millions of rows in Oracle PL/SQL projects, one of the most significant performance bottlenecks developers encounter is the Context Switch.

When processing data using traditional row-by-row LOOP structures, the database repeatedly switches execution between the PL/SQL engine and the SQL engine. While this overhead is negligible for small datasets, it degrades processing speeds dramatically as volume scales.

In this guide, we will explore the mechanics of Context Switching and demonstrate how to leverage Oracle’s BULK COLLECT and FORALL constructs to achieve 10x to 100x performance gains.

1. What Is a Context Switch & Why Does It Impact Performance?

During PL/SQL execution, two distinct engines cooperate:

1. PL/SQL Engine: Executes procedural logic, loops, conditional statements, and variable assignments.
2. SQL Engine: Executes database queries and DML statements (SELECT, INSERT, UPDATE, DELETE).
3. When using a standard cursor loop like FOR rec IN (SELECT ...), every single iteration forces control to hand off from the PL/SQL engine to the SQL engine and back.

[PL/SQL Engine] ---> (Context Switch) ---> [SQL Engine]  (Repeated for every row!)

For a task processing 100,000 rows, this transfer occurs 100,000 times. Bulk Processing eliminates this overhead by transferring data in batches rather than row by row.

2. Optimizing Data Fetching: BULK COLLECT

BULK COLLECT fetches query results into PL/SQL collections (tables/arrays) in a single operation, drastically reducing context switches during retrieval.

Traditional Approach (Row-by-Row)

SQL

DECLARE
    CURSOR c_emp IS SELECT employee_id, salary FROM employees;
BEGIN
    FOR r IN c_emp LOOP
        -- Context switch occurs on every iteration
        DBMS_OUTPUT.PUT_LINE(r.employee_id || ' - ' || r.salary);
    END LOOP;
END;
/

Optimized Approach with BULK COLLECT

Best Practice: Always use the LIMIT clause when processing large datasets to prevent excessive memory consumption (PGA) and potential Out-of-Memory errors.

SQL

DECLARE
    TYPE t_emp_rec IS RECORD (
        emp_id employees.employee_id%TYPE,
        sal    employees.salary%TYPE
    );
    TYPE t_emp_list IS TABLE OF t_emp_rec;
    
    l_emps t_emp_list;
    
    CURSOR c_emp IS 
        SELECT employee_id, salary FROM employees;
BEGIN
    OPEN c_emp;
    LOOP
        -- Fetch records in batches of 1,000
        FETCH c_emp BULK COLLECT INTO l_emps LIMIT 1000;
        
        EXIT WHEN l_emps.COUNT = 0;
        
        -- Process batch in memory without context switching
        FOR i IN 1..l_emps.COUNT LOOP
            -- Business logic goes here
            NULL; 
        END LOOP;
        
    END LOOP;
    CLOSE c_emp;
END;
/

3. Optimizing Data Modifications: FORALL

While BULK COLLECT accelerates data retrieval, FORALL optimizes DML execution (INSERT, UPDATE, DELETE) by passing an entire collection to the SQL engine in a single batch call.

Example Using FORALL

SQL

DECLARE
    TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
    l_emp_ids t_emp_ids;
BEGIN
    -- 1. Fetch eligible employee IDs into a collection
    SELECT employee_id 
    BULK COLLECT INTO l_emp_ids
    FROM employees 
    WHERE department_id = 80;

-- 2. Execute batch update using FORALL
    IF l_emp_ids.COUNT > 0 THEN
        FORALL i IN 1..l_emp_ids.COUNT
            UPDATE employees
            SET salary = salary * 1.10
            WHERE employee_id = l_emp_ids(i);
            
        COMMIT;
    END IF;
END;
/

Key Rule: FORALL is not a procedural loop; it is a batch instruction sent directly to the SQL engine. Consequently, a FORALL block can contain only one DML statement.

4. Robust Error Handling: SAVE EXCEPTIONS

By default, if an error (such as a constraint violation) occurs during a FORALL execution, the entire batch terminates immediately.

Get Ebru Karadeniz’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

To prevent a single bad row from rolling back valid operations, use the SAVE EXCEPTIONS clause. It allows valid records to process while logging exceptions into the SQL%BULK_EXCEPTIONS array.

SQL

DECLARE
    TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;
    l_emp_ids t_emp_ids := t_emp_ids(100, 101, -1, 102); -- -1 represents an invalid ID

    dml_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_errors, -24381); -- ORA-24381: Bulk error exception
BEGIN
    FORALL i IN 1..l_emp_ids.COUNT SAVE EXCEPTIONS
        UPDATE employees
        SET salary = salary * 1.05
        WHERE employee_id = l_emp_ids(i);
EXCEPTION
    WHEN dml_errors THEN
        DBMS_OUTPUT.PUT_LINE('Total errors encountered: ' || SQL%BULK_EXCEPTIONS.COUNT);
        
        FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('Collection Index: ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || 
                                 ' | Oracle Error Code: ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
        END LOOP;
END;
/

Key Takeaways & Architecture Principles

• Always Cap Memory Usage with LIMIT: Omitting LIMIT loads the entire dataset into the Process Global Area (PGA), risking severe server memory exhaustion. An ideal LIMIT size typically ranges between 1,000 and 5,000 records depending on row width.
• Prioritize Set-Based SQL First: The fastest PL/SQL code is the code that never runs. Whenever possible, rely on pure SQL operations such as INSERT INTO ... SELECT or MERGE. Reserve BULK COLLECT and FORALL for scenarios where procedural rules or complex API calls make pure SQL unfeasible.
• Implement Resilient Error Handling: In critical data pipelines, always pair FORALL with SAVE EXCEPTIONS to ensure robust logging and partial batch success without unhandled runtime failures.