Inserting or updating rows one at a time in a PL/SQL loop switches between the PL/SQL and SQL engines once per row. FORALL sends one DML statement to SQL with a whole collection of values, which does the work of thousands of rows in a single switch. It is the DML counterpart of BULK COLLECT, and often many times faster.
Code for This Guide
The main examples are in the examples/dynamic-bulk folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
forall i in {low .. high | indices of collection | values of index_collection}
[save exceptions]
dml_statement;The index can only subscript collections in the statement: compute any other values into collections first. SQL%ROWCOUNT gives the total rows affected and SQL%BULK_ROWCOUNT(i) the rows of each execution.
The examples write into a small work table:
Setup:
create table fare_snapshot (route_id number, fare number);
Insert and Update a Collection at Once
Example:
declare
type t_ids is table of number;
v_ids t_ids;
v_fares t_ids := t_ids();
begin
select route_id bulk collect into v_ids from routes where origin = 'DXB';
v_fares.extend(v_ids.count);
for i in 1 .. v_ids.count loop
v_fares(i) := 100 + i; -- FORALL can only subscript collections: prepare values
end loop;
forall i in 1 .. v_ids.count
insert into fare_snapshot (route_id, fare) values (v_ids(i), v_fares(i));
dbms_output.put_line(sql%rowcount || ' rows inserted in one call');
forall i in 1 .. 3
update fare_snapshot set fare = fare * 2 where route_id = v_ids(i);
for i in 1 .. 3 loop
dbms_output.put_line('route ' || v_ids(i) || ': '
|| sql%bulk_rowcount(i) || ' updated');
end loop;
rollback;
end;
/Output:
20 rows inserted in one call route 35: 1 updated route 7: 1 updated route 25: 1 updated PL/SQL procedure successfully completed.
One FORALL inserts the 20 routes from Dubai in a single call. The second updates three routes, and SQL%BULK_ROWCOUNT reports one row for each. The block rolls back.
Sparse Collections with INDICES OF
FORALL i IN 1 .. n requires every element from 1 to n to exist. INDICES OF uses only the indexes that exist.
Example:
declare
type t_numbers is table of number index by pls_integer;
v_routes t_numbers;
v_fares t_numbers;
begin
v_routes(1) := 1; v_fares(1) := 100; -- a sparse collection: indexes 1, 5, 9
v_routes(5) := 5; v_fares(5) := 500;
v_routes(9) := 9; v_fares(9) := 900;
forall i in indices of v_fares
insert into fare_snapshot values (v_routes(i), v_fares(i));
dbms_output.put_line(sql%rowcount || ' rows from a sparse collection');
rollback;
end;
/Output:
3 rows from a sparse collection PL/SQL procedure successfully completed.
The example drops the work table afterward.
How Much Faster
Copying the 11,224 crew assignments row by row and then with FORALL shows the difference.
Example:
declare
type t_crew is table of crew_assignments%rowtype;
v_crew t_crew;
v_start timestamp;
function ms_since (p_start timestamp) return number is
begin
return round(extract(second from (localtimestamp - p_start)) * 1000);
end;
begin
select * bulk collect into v_crew from crew_assignments;
v_start := localtimestamp;
for i in 1 .. v_crew.count loop
insert into crew_copy values v_crew(i); -- one statement per row
end loop;
dbms_output.put_line(v_crew.count || ' rows, row by row: ' || ms_since(v_start) || ' ms');
rollback;
v_start := localtimestamp;
forall i in 1 .. v_crew.count
insert into crew_copy values v_crew(i); -- one statement for all rows
dbms_output.put_line(v_crew.count || ' rows, FORALL: ' || ms_since(v_start) || ' ms');
rollback;
end;
/Output:
11224 rows, row by row: 54 ms 11224 rows, FORALL: 3 ms PL/SQL procedure successfully completed.
The loop takes many times longer than FORALL, and the gap grows with the number of rows. Your times will differ; the ratio will not.
Things to Know
- FORALL holds one DML statement; for several statements, write several FORALL loops.
- For millions of rows, combine BULK COLLECT ... LIMIT and FORALL in batches to bound memory.
- By default the first failing row stops FORALL; SAVE EXCEPTIONS processes the rest and reports failures afterward.
Related Guides
Conclusion
FORALL runs one DML statement for a whole collection in a single call to SQL, far faster than a row-by-row loop. Prepare values in collections first, use INDICES OF for sparse collections, check SQL%BULK_ROWCOUNT per row, and batch very large sets with BULK COLLECT LIMIT.
See my articles more often when you search Google.
