BULK COLLECT

 

Why Use BULK COLLECT?

Normally, if we fetch records one by one using a LOOP, each iteration causes a context switch, which slows down performance.

With BULK COLLECT, we fetch all records at once into a collection, reducing overhead.

DECLARE

    TYPE emp_table IS TABLE OF employees%ROWTYPE;

    emp_data emp_table;

BEGIN

    -- Fetch all employees in a single operation

    SELECT * BULK COLLECT INTO emp_data FROM employees;

 

    -- Loop through and display records

    FOR i IN emp_data.FIRST..emp_data.LAST LOOP

        DBMS_OUTPUT.PUT_LINE('ID: ' || emp_data(i).emp_id || ' Name: ' || emp_data(i).emp_name);

    END LOOP;

END;

/

Key Points:

• The BULK COLLECT INTO clause fetches all rows at once.
• The retrieved data is stored in a collection (table type variable).
• We then loop through the collection to display the data.

 

Example 2: Using BULK COLLECT with LIMIT Clause

Fetching a huge dataset at once might cause memory issues. To avoid this, we use LIMIT to fetch a fixed number of rows at a time.

DECLARE

    TYPE emp_table IS TABLE OF employees%ROWTYPE;

    emp_data emp_table;

    CURSOR c_emp IS SELECT * FROM employees;

BEGIN

    OPEN c_emp;

    LOOP

        -- Fetch 100 rows at a time

        FETCH c_emp BULK COLLECT INTO emp_data LIMIT 100;

       

        EXIT WHEN emp_data.COUNT = 0;  -- Exit when no more data

 

        -- Process the batch

        FOR i IN emp_data.FIRST..emp_data.LAST LOOP

            DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_data(i).emp_name);

        END LOOP;

    END LOOP;

    CLOSE c_emp;

END;

/

Key Points:

• LIMIT 100: Fetches only 100 rows per iteration.
• This avoids high memory usage when dealing with large datasets.

 

2. FORALL in PL/SQL

Why Use FORALL?

When performing bulk INSERT, UPDATE, or DELETE, using loops results in multiple context switches, making the operation slow.

Using FORALL, we execute the DML statement once for the entire collection, reducing the number of context switches.

Example 3: Using FORALL to Perform Bulk Insert

DECLARE

    TYPE emp_table IS TABLE OF employees%ROWTYPE;

    emp_data emp_table;

BEGIN

    -- Fetch data into collection

    SELECT * BULK COLLECT INTO emp_data FROM employees WHERE department_id = 10;

 

    -- Perform bulk insert into a backup table

    FORALL i IN emp_data.FIRST..emp_data.LAST

        INSERT INTO employees_backup VALUES emp_data(i);

 

    DBMS_OUTPUT.PUT_LINE('Bulk Insert Completed!');

END;

/

Key Points:

• The FORALL statement applies the INSERT operation to all elements in the collection at once.
• This avoids executing multiple INSERT statements inside a loop.

Example 4: Using FORALL to Perform Bulk Update

DECLARE

    TYPE emp_id_table IS TABLE OF employees.emp_id%TYPE;

    emp_ids emp_id_table;

BEGIN

    -- Fetch employee IDs in a bulk operation

    SELECT emp_id BULK COLLECT INTO emp_ids FROM employees WHERE department_id = 10;

 

    -- Perform bulk update

    FORALL i IN emp_ids.FIRST..emp_ids.LAST

        UPDATE employees SET salary = salary * 1.10 WHERE emp_id = emp_ids(i);

 

    DBMS_OUTPUT.PUT_LINE('Bulk Update Completed!');

END;

/

Key Points:

• FORALL applies the UPDATE statement to all rows in emp_ids at once.
• This approach improves performance significantly.

 

 

 

Example 5: Using FORALL to Perform Bulk Delete

DECLARE

    TYPE emp_id_table IS TABLE OF employees.emp_id%TYPE;

    emp_ids emp_id_table;

BEGIN

    -- Fetch employee IDs of employees to be deleted

    SELECT emp_id BULK COLLECT INTO emp_ids FROM employees WHERE department_id = 20;

 

    -- Perform bulk delete

    FORALL i IN emp_ids.FIRST..emp_ids.LAST

        DELETE FROM employees WHERE emp_id = emp_ids(i);

 

    DBMS_OUTPUT.PUT_LINE('Bulk Delete Completed!');

END;

/

Key Points:

• FORALL executes the DELETE operation in one batch for all IDs in emp_ids.

---

3. BULK COLLECT + FORALL – The Best Combination

A typical use case involves:

1.       Fetching records using BULK COLLECT.

2.       Performing DML operations using FORALL.

Example 6: Combining BULK COLLECT and FORALL

DECLARE

    TYPE emp_table IS TABLE OF employees%ROWTYPE;

    emp_data emp_table;

BEGIN

    -- Step 1: Fetch data using BULK COLLECT

    SELECT * BULK COLLECT INTO emp_data FROM employees WHERE department_id = 30;

 

    -- Step 2: Perform bulk insert using FORALL

    FORALL i IN emp_data.FIRST..emp_data.LAST

        INSERT INTO employees_backup VALUES emp_data(i);

 

    DBMS_OUTPUT.PUT_LINE('Bulk Insert Completed Successfully!');

END;

/

Key Points:

• BULK COLLECT fetches data efficiently.
• FORALL processes the entire batch in one go, avoiding unnecessary context switches.

---

Performance Comparison: BULK COLLECT vs Traditional Loop

Feature	BULK COLLECT	Traditional Loop
Fetching Rows	Multiple at once	One by one
Context Switching	Reduced	High
Memory Usage	Higher	Lower
Performance	Fast	Slow

---

Conclusion

• BULK COLLECT is useful for fast data retrieval.
• FORALL optimizes bulk DML operations (INSERT, UPDATE, DELETE).
• Using them together significantly improves performance.