在11.2 版本中使用DBMS_PARALLEL_EXECUTE包批量并行递增式的更新表。
1 把数据集分割成小的块
2 在每一个块上以并行的方式应用update语句，在每个块执行完成后，提交！
此更新技术有如下好处：
1 在执行update的时候，仅仅锁住一个shunk而非锁住整个表！
2 因为每个chunk 执行完毕就提交，所以当update操作失败后，之前变更的并不会回滚！
3 减小回滚空间的使用
4 提高性能
DBMS_PARALLEL_EXECUTE 使用三种方法来将一个表的数据分割成chunk
CREATE_CHUNKS_BY_NUMBER_COL : 通过指定的字段来切割表
CREATE_CHUNKS_BY_ROWID : 通过ROWID来切割表
CREATE_CHUNKS_BY_SQL : 通过用户提供的sql语句来切割表
前期准备：
使用上述功能的用户必须拥有CREATE JOB 权限，执行DBMS_SQL的权限，因为CHUNK_BY_SQL, RUN_TASK, 和RESUME_TASK
conn /as sysdba
GRANT Create Session, Resource to yang identified by yang;
GRANT CREATE JOB TO yang;
YANG@yangdb-rac3> INSERT /*+ APPEND */ INTO yangtab
2 SELECT level,
3 'Description for ' || level,
4 CASE
5 WHEN MOD(level, 5) = 0 THEN 10
6 WHEN MOD(level, 3) = 0 THEN 20
7 ELSE 30
8 END
9 FROM dual
10 CONNECT BY level <= 500000;
500000 rows created.
YANG@yangdb-rac3>
YANG@yangdb-rac3> commit;
Commit complete.
YANG@yangdb-rac3> SELECT num_col, COUNT(*) FROM yangtab
2 GROUP BY num_col
3 ORDER BY num_col;
NUM_COL COUNT(*)
---------- ----------
10 100000
20 133333
30 266667
1. 使用 CREATE_CHUNKS_BY_ROWID
BEGIN
DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task');
END;
/
YANG@yangdb-rac3> DECLARE
2 l_task VARCHAR2(30) := 'test_task';
3 l_sql_stmt VARCHAR2(32767);
4 l_try NUMBER;
5 l_status NUMBER;
6 BEGIN
7 -- Create the TASK
8 DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);
9
10 -- Chunk the table by the ROWID
11 DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => l_task,
12 table_owner => 'YANG',
13 table_name => 'YANGTAB',
14 by_row => TRUE,
15 chunk_size => 10000);
16 -- DML to be execute in parallel
17 l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ yangtab t SET t.num_col = t.num_col + 10 WHERE rowid BETWEEN :start_id AND :end_id';
18 -- Run the task
19 DBMS_PARALLEL_EXECUTE.run_task(task_name => l_task,
20 sql_stmt => l_sql_stmt,
21 language_flag => DBMS_SQL.NATIVE,
22 parallel_level => 10);
23
24 -- If there is error, RESUME it for at most 2 times.
25 l_try := 0;
26 l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
27 WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
28 Loop
29 l_try := l_try + 1;
30 DBMS_PARALLEL_EXECUTE.resume_task(l_task);
31 l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
32 END LOOP;
33 -- Done with processing; drop the task
34 DBMS_PARALLEL_EXECUTE.drop_task(l_task);
35 END;
36 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:26.27
YANG@yangdb-rac3> SELECT num_col, COUNT(*)
2 FROM yangtab
3 GROUP BY num_col
4 ORDER BY num_col;
NUM_COL COUNT(*)
---------- ----------
20 100000
30 133333
40 266667
2. 使用 CREATE_CHUNKS_BY_NUMBER_COL
YANG@yangdb-rac3> CREATE OR REPLACE PROCEDURE process_update (p_start_id IN NUMBER, p_end_id IN NUMBER) AS
2 BEGIN
3 UPDATE /*+ ROWID (dda) */ yangtab t
4 SET t.num_col = t.num_col + 10
5 WHERE id BETWEEN p_start_id AND p_end_id;
6 END;
7 /
Procedure created.
YANG@yangdb-rac3> DECLARE
2 l_task VARCHAR2(30) := 'test_task';
3 l_sql_stmt VARCHAR2(32767);
4 l_try NUMBER;
5 l_status NUMBER;
6 BEGIN
7 -- Create the TASK
8 DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);
9
10 -- Chunk the table by the ROWID
11 DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => l_task,
12 table_owner => 'YANG',
13 table_name => 'YANGTAB',
14 by_row => TRUE,
15 chunk_size => 10000);
16 -- DML to be execute in parallel
17 l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ yangtab t SET t.num_col = t.num_col + 10 WHERE rowid BETWEEN :start_id AND :end_id';
18 -- Run the task
19 DBMS_PARALLEL_EXECUTE.run_task(task_name => l_task,
20 sql_stmt => l_sql_stmt,
21 language_flag => DBMS_SQL.NATIVE,
22 parallel_level => 10);
23
24 -- If there is error, RESUME it for at most 2 times.
25 l_try := 0;
26 l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
27 WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
28 Loop
29 l_try := l_try + 1;
30 DBMS_PARALLEL_EXECUTE.resume_task(l_task);
31 l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
32 END LOOP;
33 -- Done with processing; drop the task
34 DBMS_PARALLEL_EXECUTE.drop_task(l_task);
35 END;
36 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.18
YANG@yangdb-rac3> SELECT num_col, COUNT(*)
2 FROM yangtab
3 GROUP BY num_col
4 ORDER BY num_col;
NUM_COL COUNT(*)
---------- ----------
30 100000
40 133333
50 266667
Elapsed: 00:00:00.12
3. 使用 CREATE_CHUNKS_BY_SQL.
BEGIN
DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task');
END;
/
YANG@yangdb-rac3> DECLARE
2 l_chunk_sql VARCHAR2(1000);
3 l_sql_stmt VARCHAR2(1000);
4 l_try NUMBER;
5 l_status NUMBER;
6 BEGIN
7-- Create the TASK
8 DBMS_PARALLEL_EXECUTE.CREATE_TASK ('test_task');
9-- Chunk the table by NUM_COL
10 l_chunk_sql := 'SELECT DISTINCT num_col, num_col FROM yangtab';
11 DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL('test_task', l_chunk_sql, false);
12-- Execute the DML in parallel
13-- the WHERE clause contain a condition on num_col, which is the chunk
14-- column. In this case, grouping rows is by num_col.
15 l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ yangtab t SET t.num_col = t.num_col + 10 WHERE num_col BETWEEN :start_id AND :end_id';
16 DBMS_PARALLEL_EXECUTE.RUN_TASK('test_task', l_sql_stmt, DBMS_SQL.NATIVE,
17 parallel_level => 10);
18-- If there is error, RESUME it for at most 2 times.
19 L_try := 0;
20 L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('test_task');
21 WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED)
22 Loop
23 L_try := l_try + 1;
24 DBMS_PARALLEL_EXECUTE.RESUME_TASK('test_task');
25 L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('test_task');
26 END LOOP;
27-- Done with processing; drop the task
28 DBMS_PARALLEL_EXECUTE.DROP_TASK('test_task');
29 end;
30 /
PL/SQL procedure successfully completed.
YANG@yangdb-rac3> SELECT num_col, COUNT(*)
2 FROM yangtab
3 GROUP BY num_col
4 ORDER BY num_col;
NUM_COL COUNT(*)
---------- ----------
42 100000
52 133333
62 266667