并行DML是让批量DML加速的一种非常强大的方法。然而,在单个事务中应用所有的变更也是有缺点的。这会导致长时间的锁,要求大量的回滚段,而且当操作失败时,将带来昂贵的回滚操作的风险。
在Oracle 11gR2中引入的DBMS_PARALLEL_EXECUTE帮助解决了这个难题。它允许用更小的”块“来执行并行DML,且每个块都可以独立提交。任何一个单独的块操作发生失败时,包支持重新启动这个任务。
DECLARE
v_stmt VARCHAR2(2000);
v_taskname VARCHAR2(200) := 'my parallel task demo3';
v_status NUMBER;
BEGIN
dbms_parallel_execute.create_task(task_name => v_taskname,
COMMENT => 'demo');
dbms_parallel_execute.create_chunks_by_rowid(task_name => v_taskname,
table_owner => USER,
table_name => 'TEST_SALES',
by_row => TRUE,
chunk_size => 100);
v_stmt := 'update TEST_SALES set amount_sold = amount_sold*10 where rowid between :start_id and :end_id ';
--这里的绑定变量占位符:start_id与end_id都不能随意替换成其他标识
dbms_parallel_execute.run_task(task_name => v_taskname,
sql_stmt => v_stmt,
language_flag => dbms_sql.native,
parallel_level => 4);
v_status := dbms_parallel_execute.task_status(task_name => v_taskname);
IF v_status = dbms_parallel_execute.finished THEN
dbms_parallel_execute.drop_task(task_name => v_taskname);
ELSE
dbms_parallel_execute.drop_task(task_name => v_taskname);
raise_application_error(-20001,
v_taskname || ' finished with error!!!',
TRUE);
END IF;
END;
摘自:Oracle性能优化求生指南
在Oracle 11gR2中引入的DBMS_PARALLEL_EXECUTE帮助解决了这个难题。它允许用更小的”块“来执行并行DML,且每个块都可以独立提交。任何一个单独的块操作发生失败时,包支持重新启动这个任务。
DECLARE
v_stmt VARCHAR2(2000);
v_taskname VARCHAR2(200) := 'my parallel task demo3';
v_status NUMBER;
BEGIN
dbms_parallel_execute.create_task(task_name => v_taskname,
COMMENT => 'demo');
dbms_parallel_execute.create_chunks_by_rowid(task_name => v_taskname,
table_owner => USER,
table_name => 'TEST_SALES',
by_row => TRUE,
chunk_size => 100);
v_stmt := 'update TEST_SALES set amount_sold = amount_sold*10 where rowid between :start_id and :end_id ';
--这里的绑定变量占位符:start_id与end_id都不能随意替换成其他标识
dbms_parallel_execute.run_task(task_name => v_taskname,
sql_stmt => v_stmt,
language_flag => dbms_sql.native,
parallel_level => 4);
v_status := dbms_parallel_execute.task_status(task_name => v_taskname);
IF v_status = dbms_parallel_execute.finished THEN
dbms_parallel_execute.drop_task(task_name => v_taskname);
ELSE
dbms_parallel_execute.drop_task(task_name => v_taskname);
raise_application_error(-20001,
v_taskname || ' finished with error!!!',
TRUE);
END IF;
END;
摘自:Oracle性能优化求生指南
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8520577/viewspace-1654881/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8520577/viewspace-1654881/