One UPDATE of millions of rows makes one huge transaction: long locks, lots of undo, and a long rollback if it fails. DBMS_PARALLEL_EXECUTE splits the work into chunks, by ROWID or by a number column, and runs the statement for each chunk in its own transaction, several chunks at a time through Scheduler jobs.
Code for This Guide
The main examples are in the examples/pkg-data-ops 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
dbms_parallel_execute.create_task('TASK');
dbms_parallel_execute.create_chunks_by_number_col('TASK', schema, table,
table_column, chunk_size);
-- or create_chunks_by_rowid('TASK', schema, table, by_row, chunk_size)
dbms_parallel_execute.run_task('TASK', sql_stmt, language_flag, parallel_level);
dbms_parallel_execute.task_status('TASK')
dbms_parallel_execute.drop_task('TASK');The statement uses :start_id and :end_id, which each chunk fills with its range. RUN_TASK needs the CREATE JOB privilege.
Recalculate Fares in Chunks
TICKET_FARES is a work copy of the ticket fares. The task fills a new column in ranges of 200 ticket IDs, two chunks at a time.
Example:
begin
dbms_parallel_execute.create_task('RECALC_FARES');
-- split the rows into ranges of 200 ticket IDs
dbms_parallel_execute.create_chunks_by_number_col('RECALC_FARES', user, 'TICKET_FARES',
table_column => 'TICKET_ID',
chunk_size => 200);
dbms_parallel_execute.run_task('RECALC_FARES',
sql_stmt => 'update ticket_fares
set fare_aed = round(fare * 3.6725, 2)
where ticket_id between :start_id and :end_id',
language_flag => dbms_sql.native,
parallel_level => 2); -- two scheduler jobs
dbms_output.put_line('status: ' || dbms_parallel_execute.task_status('RECALC_FARES')
|| ' (' || dbms_parallel_execute.finished || ' = FINISHED)');
end;
/
select status, count(*) as chunks from user_parallel_execute_chunks
where task_name = 'RECALC_FARES' group by status;
select count(*) as tickets, count(fare_aed) as recalculated from ticket_fares;
exec dbms_parallel_execute.drop_task('RECALC_FARES')Output:
status: 7 (7 = FINISHED)
PL/SQL procedure successfully completed.
STATUS CHUNKS
____________ _________
PROCESSED 6
TICKETS RECALCULATED
__________ _______________
1069 1069
PL/SQL procedure successfully completed.The task ends with status 7, FINISHED. Six chunks were processed, and all 1,069 tickets have a fare in dirhams. The example drops the task and the table afterward.
Things to Know
- Each chunk commits on its own, so a failure leaves earlier chunks done; RESUME_TASK retries the failed ones.
- USER_PARALLEL_EXECUTE_CHUNKS shows each chunk's status and any error.
- The statement must be safe to run per range: it should not depend on rows outside its chunk.
Related Guides
Conclusion
DBMS_PARALLEL_EXECUTE runs large updates as many small committed chunks, in parallel Scheduler jobs. Create a task, chunk the table by a number column or ROWID, run the statement with :start_id and :end_id, and resume it if chunks fail.
