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
|
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