文档基于Oracle 11.2版本

DBMS_PARALLEL_EXECUTE的介绍

Oracle建议更新大量数据时，使用此技术。

DBMS_PARALLEL_EXECUTE包提供一种能够并行增量更新表的方法。

优点

DBMS_PARALLEL_EXECUTE包的优点是：

• 只会导致行级锁，锁定时间较短，而不是锁定整个表
• 在执行更新的过程中出现故障，不会丢失已经更新完毕的行。
• undo的使用消耗降低。
• 提高性能。

  原理

  DBMS_PARALLEL_EXECUTE包会以两个步骤来并行增量更新表的数据：

1. 将表中的行分组为较小的块。
2. 在这些块上并行执行指定的语句，并在处理完每个块后提交。

   限制

   如果要在system权限上运行，需要将要更新的表授权到system上才会运行。

要并行执行块，用户必须具有create job权限。

DBMS_PARALLEL_EXECUTE子程序

ADM_DROP_CHUNKS

功能：删除指定用户拥有的指定任务的所有块
语法：

DBMS_PARALLEL_EXECUTE.ADM_DROP_CHUNKS (
   task_owner      IN  VARCHAR2,
   task_name       IN  VARCHAR2);

参数：

• task_owner 任务拥有者
• task_name 任务名称

ADM_DROP_TASK

功能：删除指定用户的任务和相关的所有块
语法：

DBMS_PARALLEL_EXECUTE.ADM_DROP_TASK (
   task_owner      IN  VARCHAR2,
   task_name       IN  VARCHAR2);

参数：

• task_owner 任务拥有者
• task_name 任务名称

ADM_TASK_STATUS

功能：返回任务状态
语法：

DBMS_PARALLEL_EXECUTE.ADM_TASK_STATUS  (
   task_owner      IN  VARCHAR2,
   task_name       IN  VARCHAR2)
 RETURN NUMBER;

参数：

• task_owner 任务拥有者
• task_name 任务名称

ADM_STOP_TASK

功能：停止指定用户和相关作业从属的任务

DBMS_PARALLEL_EXECUTE.ADM_STOP_TASK (
   task_owner      IN  VARCHAR2,
   task_name       IN  VARCHAR2);

参数：

• task_owner 任务拥有者
• task_name 任务名称

CREATE_TASK

功能：以当前用户创建任务
语法：

DBMS_PARALLEL_EXECUTE.CREATE_TASK (
   task_name        IN   VARCHAR2,
   comment          IN   VARCHAR2 DEFAULT NULL);

参数：

• task_name 任务名称，限制长度必须小于或等于128字节的字符串。
• comment 注释，限制必须小于4000字节。

示例：

begin
  dbms_parallel_execute.create_task('mytask','test1');
end;

CREATE_CHUNKS_BY_NUMBER_COL

备注：指定的列必须是number类型，此过程取列的最小值和最大值，然后根据chunk_size平均划分块，范围是： min_id_val min_id_val+1chunk_size-1 min_id_val+1chunk_size min_id_val+2chunk_size-1 … min_id_val+ichunk_size max_id_val

功能：按任务关联的表的指定列分块
语法：

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_NUMBER_COL (
   task_name       IN  VARCHAR2,
   table_owner     IN  VARCHAR2,
   table_name      IN  VARCHAR2,
   table_column    IN  VARCHAR2,
   chunk_size      IN  NUMBER);

参数：

• task_name 任务名称
• table_owner 表拥有者
• table_name 表名
• table_column 表列
• chunk_size 块范围

示例：

begin
  DBMS_PARALLEL_EXECUTE.create_chunks_by_number_col('mytask','TEST','TEST','AAA',1000);
end;

CREATE_CHUNKS_BY_ROWID

备注：要分块的表必须是物理表，不能用于索引组织表。

功能：以ROWID将任务关联的表分块
语法：

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID (
   task_name       IN  VARCHAR2,
   table_owner     IN  VARCHAR2,
   table_name      IN  VARCHAR2,
   by_row          IN  BOOLEAN,
   chunk_size      IN  NUMBER);

参数：

• task_name 任务名称
• table_owner 表所有者
• table_name 表名
• by_row 如果chunk_size是指行数，则该值为TRUE；否则，chunk_size指块数
• chunk_size 每次提交要处理的行数或块数

示例：

begin
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'TEST', 'TEST', true, 10000);
end;

CREATE_CHUNKS_BY_SQL

SQL语句必须有两列，:start_id和end_id，如果以rowid分，则两列必须是rowid类型，如果以number来分，则两列必须是number类型。

功能：用户提供select语句将任务关联的表分块
语法：

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL ( 
   task_name IN VARCHAR2, 
   sql_statement IN CLOB, 
   by_rowid IN BOOLEAN);

参数：

• task_name 任务名称
• sql_statement 返回块范围的SQL语句
• by_rowid 为true表示是用rowid来分

示例：

begin
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL('mytask', 'select aaa,bbb from test.test', false); 
end;

DROP_TASK

功能：删除任务和所有关联块
语法：

DBMS_PARALLEL_EXECUTE.DROP_TASK ( 
   task_name IN VARCHAR2);

参数：

• task_name 任务名称

示例：

begin
  DBMS_PARALLEL_EXECUTE.drop_task('mytask');
end;

DROP_CHUNKS

功能：删除任务的所有块
语法：

DBMS_PARALLEL_EXECUTE.DROP_CHUNKS ( 
   task_name IN VARCHAR2);

参数：

• task_name 任务名称

示例：

begin
  DBMS_PARALLEL_EXECUTE.drop_task('mytask');
end;

GENERATE_TASK_NAME

功能：返回任务唯一名称
语法：

DBMS_PARALLEL_EXECUTE.GENERATE_TASK_NAME (
   prefix      IN      VARCHAR2 DEFAULT 'TASK$_') 
 RETURN VARCHAR2;

参数：

• prefix 生成任务名称时使用的前缀，默认值TASK$_

GET_NUMBER_COL_CHUNK

功能：待测试

GET_ROWID_CHUNK

功能：待测试

PURGE_PROCESSED_CHUNKS

功能：删除状态为processed或processed_with_error的所有已处理块
语法：

DBMS_PARALLEL_EXECUTE.PURGE_PROCESSED_CHUNKS ( 
   task_name IN VARCHAR2);

参数：

• task_name 任务名称

RESUME_TASK

功能：

RUN_TASK

功能：以块并行的方式执行指定的SQL语句
语法：

DBMS_PARALLEL_EXECUTE.RUN_TASK (
   task_name                     IN  VARCHAR2,
   sql_stmt                      IN  CLOB,
   language_flag                 IN  NUMBER,
   edition                       IN  VARCHAR2  DEFAULT NULL,
   apply_crossedition_trigger    IN  VARCHAR2  DEFAULT NULL,
   fire_apply_trigger            IN  BOOLEAN   DEFAULT TRUE,
   parallel_level                IN  NUMBER    DEFAULT 0,
   job_class                     IN  VARCHAR2  DEFAULT 'DEFAULT_JOB_CLASS');

参数：

• task_name 任务名称
• sql_stmt SQL语句，必须有:start_id和:end_id占位符。
• language_flag 确定Oracle如何处理SQL语句，选项如下：
  • V6(或 0) 指定版本6
  • NATIVE(或 1) 指定程序链接到数据库的正常操作
  • V7(或 2) 指定版本7
• edition 指定运行语句的版本，默认为当前版本。
• apply_crossedition_trigger 指定应用于SQL的触发器名
• fire_apply_trigger 指示触发器是自己运行，还是作为选择其他触发器的指引。
• parallel_level 并行作业的数量；串行时为0，null表示使用默认并行度
• job_class 作业属于指定的作业类

示例：

begin
  DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', 'update test.test set code=1 WHERE rowid BETWEEN :start_id AND :end_id', DBMS_SQL.NATIVE,parallel_level => 4);
end;

SET_CHUNK_STATUS

功能：设置块的状态

STOP_TASK

功能：停止任务和相关作业从属
语法：

DBMS_PARALLEL_EXECUTE.STOP_TASK (
   task_name       IN VARCHAR2);

参数：

• task_name 任务名称

TASK_STATUS

此过程通常用于在执行中获取任务的状态来判断是否有错误

功能：返回任务状态
语法：

DBMS_PARALLEL_EXECUTE.TASK_STATUS (
   task_name       IN VARCHAR2);

匿名块案例

以ROWID分块

DECLARE
  l_sql_stmt VARCHAR2(1000);
  l_try NUMBER;
  l_status NUMBER;
BEGIN
  -- Create the TASK
  DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
  -- Chunk the table by ROWID
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'HR', 'EMPLOYEES', true, 100);
  -- Execute the DML in parallel
  l_sql_stmt := 'update /*+ ROWID (dda) */ EMPLOYEES e 
      SET e.salary = e.salary + 10
      WHERE rowid BETWEEN :start_id AND :end_id';
  DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,
                                 parallel_level => 10);
  -- If there is an error, RESUME it for at most 2 times.
  L_try := 0;
  L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
  WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED) 
  LOOP
    L_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask');
    L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
  END LOOP;
  -- Done with processing; drop the task
  DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');
END;
/

以表中字段列分块

DECLARE
  l_chunk_sql VARCHAR2(1000);
  l_sql_stmt VARCHAR2(1000);
  l_try NUMBER;
  l_status NUMBER;
BEGIN
  -- Create the TASK
  DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
  -- Chunk the table by MANAGER_ID
  l_chunk_sql := 'SELECT distinct manager_id, manager_id FROM employees';
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL('mytask', l_chunk_sql, false);
  -- Execute the DML in parallel
  --   the WHERE clause contain a condition on manager_id, which is the chunk
  --   column. In this case, grouping rows is by manager_id.
  l_sql_stmt := 'update /*+ ROWID (dda) */ EMPLOYEES e 
      SET e.salary = e.salary + 10
      WHERE manager_id between :start_id and :end_id';
  DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,
                                  parallel_level => 10);
  -- If there is error, RESUME it for at most 2 times.
  L_try := 0;
  L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
  WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED) 
  Loop
    L_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask');
    L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
  END LOOP;
  -- Done with processing; drop the task
  DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');
end;
/