使用 DBMS_PARALLEL_EXECUTE 更新大表

Oracle 11g 中新功能 DBMS_PARALLEL_EXECUTE 包可以使你在并行增量更新一个大表的数据，两个高层次的步骤是：

1. Group sets of rows in the table into smaller chunks.
2. Apply the desired UPDATE statement to the chunks in parallel, committing each time you have finished processing a chunk.

This technique is recommended whenever you are updating a lot of data. Its advantages are:

• You lock only one set of rows at a time, for a relatively short time, instead of locking the entire table.
• You do not lose work that has been done if something fails before the entire operation finishes.
• You reduce rollback space consumption.
• You improve performance.

Different Ways to Spilt Workload

1. CREATE_CHUNKS_BY_NUMBER_COL : Chunks the table associated with the given task by the specified column.
2. CREATE_CHUNKS_BY_ROWID : Chunks the table associated with the given task by ROWID
3. CREATE_CHUNKS_BY_SQL : Chunks the table associated with the given task by means of a user-provided SELECT statement

准备测试数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

truncate table scott . test_tab ; drop table scott . test_tab purge ; create table scott . test_tab2 ( id number , description varchar2 ( 50 ) , num_col number , constraint test_tab2_pk primary key ( id )   ) ;   insert /*+ APPEND */ into scott . test_tab2 select level , 'Description for ' || level , case when mod ( level , 5 ) = 0 then 10 when mod ( level , 3 ) = 0 then 20 else 30 end from dual connect by level <= 1000000 ; commit ;   select num_col , count ( * ) from scott . test_tab2 group by num_col order by num_col ;

注：

执行DBMS_PARALLE_EXECUTE的用户需要create job权限，DBMS_SQL的执行权限，因为CHUNK_BY_SQL, RUN_TASK, and RESUME_TASK 子程序需要使用DBMS_SQL执行查询。

1	GRANT CREATE JOB TO SCOTT ;

1.使用 CREATE_CHUNKS_BY_ROWID

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

begin dbms_parallel_execute . drop_task ( 'test_task' ) ; end ; / set timing on declare l_task varchar2 ( 30 ) : = 'test_task' ; l_sql_stmt varchar2 ( 32767 ) ; l_try number ; l_status number ; BEGIN dbms_parallel_execute . create_task ( task_name = > l_task ) ; dbms_parallel_execute . create_chunks_by_rowid ( task_name = > l_task , table_owner = > 'SCOTT' , table_name = > 'TEST_TAB' , by_row = > TRUE , chunk_size = > 10000 ) ;   l_sql_stmt : = 'update /* + ROWID(dda) */ scott.test_tab t set t.num_col=t.num_col-10 where rowid between :start_id and :end_id' ;   dbms_parallel_execute . run_task ( task_name = > l_task , sql_stmt = > l_sql_stmt , language_flag = > DBMS_SQL . NATIVE , parallel_level = > 10 ) ;   l_try : = 0 ; l_status : = dbms_parallel_execute . task_status ( l_task ) ; while ( l_try < 2 and l_status != dbms_parallel_execute . finished ) loop l_try : = l_try + 1 ; dbms_parallel_execute . resume_task ( l_task ) ; l_status : = dbms_parallel_execute . task_status ( l_task ) ; end loop ;   dbms_parallel_execute . drop_task ( l_task ) ;   END ; /   anonymous block completed Elapsed : 00 : 00 : 09.213      NUM_COL   COUNT ( * ) -- -- -- -- -- -- -- -- -- --          30      200000          40      266667          50      533333   Elapsed : 00 : 00 : 00.337

使用CREATE_CHUNKS_BY_NUMBER_COL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

create or replace procedure update_scott_test_tb ( start_id in number , end_id in number ) as begin update /*+ ROWID (dda) */ SCOTT . test_tab st set st . num_col = st . num_col + 10 where id between start_id and end_id ; end ; /   set timing on declare l_task varchar2 ( 30 ) : = 'test_task' ; l_sql_stmt varchar2 ( 32767 ) ; l_try number ; l_status number ; BEGIN dbms_parallel_execute . create_task ( task_name = > l_task ) ; dbms_parallel_execute . create_chunks_by_number_col ( task_name = > l_task , table_owner = > 'SCOTT' , table_name = > 'TEST_TAB' , table_column = > 'ID' , chunk_size = > 10000 ) ; l_sql_stmt : = 'BEGIN update_scott_test_tb(:start_id,:end_id);end;' ;   dbms_parallel_execute . run_task ( task_name = > l_task , sql_stmt = > l_sql_stmt , language_flag = > dbms_sql . native , parallel_level = > 10 ) ;   l_try : = 0 ; l_status : = dbms_parallel_execute . task_status ( l_task ) ; while ( l_try < 2 and l_status != dbms_parallel_execute . finished ) loop l_try : = l_try + 1 ; dbms_parallel_execute . resume_task ( l_task ) ; l_status : = dbms_parallel_execute . task_status ( l_task ) ; end loop ;   dbms_parallel_execute . drop_task ( l_task ) ;   end ; / anonymous block completed Elapsed : 00 : 00 : 18.343   select num_col , count ( * ) from scott . test_tab group by num_col order by num_col ;    NUM_COL   COUNT ( * ) -- -- -- -- -- -- -- -- -- --          40      200000          50      266667          60      533333   Elapsed : 00 : 00 : 00.310

 
使用CREATE_CHUNKS_BY_SQL