Oracle PL/SQL Performance: BULK COLLECT and FORALL (Updated Guide)
When PL/SQL executes SQL row-by-row, Oracle performs many context switches between the PL/SQL and SQL engines.
For large datasets, this can become a major bottleneck.
Two features help solve this:
- BULK COLLECT: fetch many rows into a collection in one operation.
- FORALL: send many DML operations (INSERT/UPDATE/DELETE) in one operation.
Used together, they reduce context switches and can significantly improve throughput.
1) Test Data Setup
CREATE TABLE bulk_collect_test AS
SELECT owner, object_name, object_id
FROM all_objects;
## Oracle PL/SQL Performance: BULK COLLECT and FORALL (Updated Guide)
When PL/SQL executes SQL row-by-row, Oracle performs many **context switches** between the PL/SQL and SQL engines.
For large datasets, this can become a major bottleneck.
Two features help solve this:
- **BULK COLLECT**: fetch many rows into a collection in one operation.
- **FORALL**: send many DML operations (INSERT/UPDATE/DELETE) in one operation.
Used together, they reduce context switches and can significantly improve throughput.
---
## 1) Test Data Setup
```sql
CREATE TABLE bulk_collect_test AS
SELECT owner, object_name, object_id
FROM all_objects;
```
---
## 2) BULK COLLECT Basics
### Row-by-row fetch (slower pattern)
```sql
SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF bulk_collect_test%ROWTYPE;
l_tab t_tab := t_tab();
l_start NUMBER;
BEGIN
l_start := DBMS_UTILITY.get_time;
FOR r IN (
SELECT owner, object_name, object_id
FROM bulk_collect_test
) LOOP
l_tab.EXTEND;
l_tab(l_tab.LAST) := r;
END LOOP;
DBMS_OUTPUT.put_line('Row-by-row: ' || (DBMS_UTILITY.get_time - l_start));
END;
/
```
### BULK COLLECT fetch (faster pattern)
```sql
SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF bulk_collect_test%ROWTYPE;
l_tab t_tab;
l_start NUMBER;
BEGIN
l_start := DBMS_UTILITY.get_time;
SELECT owner, object_name, object_id
BULK COLLECT INTO l_tab
FROM bulk_collect_test;
DBMS_OUTPUT.put_line('Bulk collect: ' || (DBMS_UTILITY.get_time - l_start));
END;
/
```
> In most environments, BULK COLLECT is much faster for large result sets.
---
## 3) Use LIMIT to Control Memory
Fetching everything at once can consume too much PGA memory.
For large data, use chunking:
```sql
SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF bulk_collect_test%ROWTYPE;
l_tab t_tab;
CURSOR c_data IS
SELECT owner, object_name, object_id
FROM bulk_collect_test;
BEGIN
OPEN c_data;
LOOP
FETCH c_data BULK COLLECT INTO l_tab LIMIT 1000;
EXIT WHEN l_tab.COUNT = 0;
-- Process l_tab here
DBMS_OUTPUT.put_line('Chunk size: ' || l_tab.COUNT);
END LOOP;
CLOSE c_data;
END;
/
```
**Tip:** Start with LIMIT 500–5000 and benchmark.
---
## 4) FORALL for Bulk DML
After loading data into collections, use FORALL for fast DML:
```sql
DECLARE
TYPE t_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;
l_ids t_id_tab;
BEGIN
SELECT object_id
BULK COLLECT INTO l_ids
FROM bulk_collect_test
WHERE object_id IS NOT NULL
FETCH FIRST 10000 ROWS ONLY;
FORALL i IN 1 .. l_ids.COUNT
UPDATE bulk_collect_test
SET object_name = object_name || '_X'
WHERE object_id = l_ids(i);
COMMIT;
END;
/
```
---
## 5) Handle Partial Failures with SAVE EXCEPTIONS
If one row fails, you may still want other rows processed:
```sql
DECLARE
TYPE t_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;
l_ids t_id_tab;
BEGIN
SELECT object_id BULK COLLECT INTO l_ids
FROM bulk_collect_test
WHERE object_id IS NOT NULL
FETCH FIRST 1000 ROWS ONLY;
BEGIN
FORALL i IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
DELETE FROM bulk_collect_test
WHERE object_id = l_ids(i);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -24381 THEN
FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.put_line(
'Error at index ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
', ORA-' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE
);
END LOOP;
ELSE
RAISE;
END IF;
END;
COMMIT;
END;
/
```
---
## 6) Practical Guidance
- Prefer **set-based SQL** first (single SQL statement).
- Use **BULK COLLECT + FORALL** when procedural logic is required.
- Always benchmark:
- row-by-row
- BULK COLLECT with different LIMIT sizes
- FORALL batch sizes
- Avoid very large in-memory collections unless necessary.
- Commit in reasonable batches for long-running jobs.
---
## 7) Oracle 10g+ Note
Oracle can optimize some cursor FOR loops internally, but explicit bulk processing still gives you:
- control over batch size,
- predictable memory behavior,
- better tuning for your specific workload.
---
## Conclusion
For high-volume PL/SQL processing, BULK COLLECT and FORALL remain core performance tools.
Use LIMIT, measure carefully, and tune batch sizes based on row width, memory budget, and DML complexity.
