dbms_parallel_execute包的介绍

此包使用去大批量数据INSERT,DELETE,UPDATE还支持MERGE 并且不锁表
经过测试几乎1分钟1000万左右不过还得说必须服务器性能好
1.赋权
grant create job to SCOTT;
grant execute on dbms_scheduler to SCOTT
grant execute on dbms_parallel_execute to SCOTT;
2.包含3种形式
--1?CREATE_CHUNKS_BY_ROWID
BEGIN
  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   => 'test_task',
                                               table_owner => 'TEST',
                                               table_name  => 'TEST_TAB',
                                               by_row      => TRUE,
                                               chunk_size  => 10000);
END;
--2.?CREATE_CHUNKS_BY_NUMBER_COL
BEGIN
  DBMS_PARALLEL_EXECUTE.create_chunks_by_number_col(task_name    => 'test_task',
                                                    table_owner  => 'TEST',
                                                    table_name   => 'TEST_TAB',
                                                    table_column => 'ID',
                                                    chunk_size   => 10000);
END;
--3.?CREATE_CHUNKS_BY_SQL
DECLARE
  l_stmt CLOB;
BEGIN
  l_stmt := 'SELECT DISTINCT num_col, num_col FROM test_tab';

  DBMS_PARALLEL_EXECUTE.create_chunks_by_sql(task_name => 'test_task',
                                             sql_stmt  => l_stmt,
                                             by_rowid  => FALSE);
END;
---重要的几个视图监控

select task_name, status from dba_parallel_execute_tasks;
select * from user_parallel_execute_chunks;
SELECT job_name, status
FROM   user_scheduler_job_run_details
WHERE  job_name LIKE (SELECT job_prefix || '%'
                      FROM   user_parallel_execute_tasks
                      WHERE  task_name = 'test_task');
--大批量插入一例
CREATE OR REPLACE PROCEDURE process_insert_chenduan_yql is
/*by  方便大批量数据处理*/
   vc_sql varchar2(1000);
   n_try number;
   n_status number;
   begin
   --create a task
   dbms_parallel_execute.create_task(task_name => 'yql_insert');
   --By Rowid
   dbms_parallel_execute.create_chunks_by_rowid(task_name => 'yql_insert',
    table_owner => user,table_name => 'YQL_TEST',by_row =>true,chunk_size => 10000);
    vc_sql:='insert /*+ ROWID (dda) */ into YQL_TEST_bck(A,B,C,D)
    select  A,B,C,D
        from  YQL_TEST CCS  WHERE ccs.ROWID between :start_id and :end_id';
 --  vc_sql := 'update /*+rowid(dda)*/t_target set owner=to_char(length(owner)) where rowid between :start_id and :end_id';
   dbms_parallel_execute.run_task(task_name => 'yql_insert',sql_stmt =>vc_sql,language_flag =>dbms_sql.native,parallel_level =>16);
 --防止失败后重启
   n_try := 0;
   n_status := dbms_parallel_execute.task_status('yql_insert');
   while (n_try<2 and (n_status != dbms_parallel_execute.FINISHED)) loop
      n_try := n_try + 1;
      dbms_parallel_execute.resume_task('yql_insert');
      n_status := dbms_parallel_execute.task_status('yql_insert');
   end loop;
   dbms_output.put_line(''||n_try);
   dbms_parallel_execute.drop_task('yql_insert');
END;
想看详细介绍官网链接：
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_parallel_ex.htm
或者这里介绍：
http://www.cnblogs.com/tracy/archive/2011/06/09/2076324.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29733230/viewspace-1683809/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29733230/viewspace-1683809/