使用 DBMS_PARALLEL_EXECUTE 更新大表

来源：互联网发布：数据库中省略为什么编辑：程序博客网时间：2024/06/01 16:57

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

Group sets of rows in the table into smaller chunks.
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

CREATE_CHUNKS_BY_NUMBER_COL : Chunks the table associated with the given task by the specified column.
CREATE_CHUNKS_BY_ROWID : Chunks the table associated with the given task by ROWID
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
truncatetablescott.test_tab;
droptablescott.test_tabpurge;
createtablescott.test_tab2(
idnumber,
descriptionvarchar2(50),
num_colnumber,
constrainttest_tab2_pkprimarykey(id)
 
);
 
insert/*+ APPEND */intoscott.test_tab2
selectlevel,
'Description for '||level,
case
whenmod(level,5)=0then10
whenmod(level,3)=0then20
else30
end
fromdual
connectbylevel<=1000000;
commit;
 
selectnum_col,count(*)fromscott.test_tab2
groupbynum_col
orderbynum_col;

注：

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

1
GRANTCREATEJOBTOSCOTT;

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;
/
settimingon
declare
l_taskvarchar2(30):='test_task';
l_sql_stmtvarchar2(32767);
l_trynumber;
l_statusnumber;
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<2andl_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);
endloop;
 
dbms_parallel_execute.drop_task(l_task);
 
END;
/
 
anonymousblockcompleted
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
createorreplaceprocedureupdate_scott_test_tb(start_idinnumber,end_idinnumber)as
begin
update/*+ ROWID (dda) */SCOTT.test_tabst
setst.num_col=st.num_col+10
whereidbetweenstart_idandend_id;
end;
/
 
settimingon
declare
l_taskvarchar2(30):='test_task';
l_sql_stmtvarchar2(32767);
l_trynumber;
l_statusnumber;
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<2andl_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);
endloop;
 
dbms_parallel_execute.drop_task(l_task);
 
end;
/
anonymousblockcompleted
Elapsed:00:00:18.343
 
selectnum_col,count(*)fromscott.test_tab
groupbynum_col
orderbynum_col;
  NUM_COL  COUNT(*)
--------------------
        40    200000
        50    266667
        60    533333
 
Elapsed:00:00:00.310

使用CREATE_CHUNKS_BY_SQL

0 0