How to Update Large Tables in Chunks with DBMS_PARALLEL_EXECUTE

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

• How to Use FORALL for Bulk DML in PL/SQL
• How to Create Jobs with DBMS_SCHEDULER.CREATE_JOB

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.