oracle dbms_parallel_execute,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/，如需转载，请注明出处，否则将追究法律责任。