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 -> DBACLASS
TABLE_NAME -> TEST100
DML STATEMENT -> Update dbaclass.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 =>'DBACLASS', 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 dbaclass.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.