From oracle 11gr2, DBMS_PARALLEL_EXECUTE package can be used for updating large tables in parallel using chunk option.

Basically, it does two steps.

1. Divides the table data into small chunks.
2. Apply DML change on each chunk parallely.

This method improves the performance the large DML operations, without consuming too much of rollback segment.

Lets check the below example.

TABLE_OWNER   -> EXATREE
TABLE_NAME    ->  TEST100
DML STATEMENT -> Update exatree.test100 set OBJECT_ID=10 where NAMESPACE=1;

1. Create one task:

 SQL> execute DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME=>'query_test100_update');

PL/SQL procedure successfully completed.

2. CHUNK the table by row_id:

BEGIN
    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID
  (
                TASK_NAME=> 'query_test100_update',
                TABLE_OWNER =>'EXATREE',
                TABLE_NAME => 'TEST100',
                BY_ROW=> TRUE,
                CHUNK_SIZE => 10000
  );
END;
/

3. Run the DML update procedure(RUN_TASK)

SET SERVEROUTPUT ON
DECLARE
  l_sql_stmt VARCHAR2(32767);
BEGIN 
 -- DML to be execute in parallel
  l_sql_stmt := 'Update exatree.test100 set OBJECT_ID=10 where NAMESPACE=1 and rowid BETWEEN :start_id AND :end_id';

  -- Run the task
  DBMS_PARALLEL_EXECUTE.RUN_TASK
  (
    TASK_NAME      => 'query_test100_update',
    SQL_STMT       => l_sql_stmt,
    LANGUAGE_FLAG  => DBMS_SQL.NATIVE,
    PARALLEL_LEVEL => 10
  );  
END;
/

 
PL/SQL procedure successfully completed.

Elapsed: 00:00:21.07

DML completed successfully and it took 21 seconds only.

 

 

 

PL/SQL Database-Wiki