oracle 11g 及更高级版本，利用数据内部并发机制执行update，大大提高执行效率

1.创建task:
exec DBMS_PARALLEL_EXECUTE.create_task (task_name => ‘my_task’);

2.查询创建task
select * from user_parallel_execute_tasks;
select task_name,status from user_parallel_execute_tasks where table_name=‘test’;

select dbms_parallel_execute.generate_task_name from dual; --默认taskName

3.将一张大表split 成多个chunks 有三种方法。
（1）CREATE_CHUNKS_BY_ROWID
（2）CREATE_CHUNKS_BY_NUMBER_COL
（3）CREATE_CHUNKS_BY_SQL

下面是rowId 分段：
exec DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => ‘my_tas’,table_owner => ‘test’,table_name => ‘test’,by_row => true,chunk_size => 100000);

4.查询rowid分段 chunks
查询task信息：
select task_name,status from user_parallel_execute_tasks where task_name=‘my_task’;

查询chunks 信息：
select chunk_id,status,start_rowid,end_rowid from user_parallel_execute_chunks where task_name = ‘my_task’ order by chunk_id;

5.执行并发parallel(:start_id 和 :end_id 是固定值)
sql = ‘update /*+ ROWID(dda) */ test.CUST_BIG_2KW_1 t set ADDRESS=function(xxx,xx) where rowid between :start_id and :end_id’
注意： function 可以是系统函数，可以是自定义函数

exec dbms_parallel_execute.run_task(task_name => ‘my_task’,sql_stmt => ‘update /*+ ROWID() */ test.CUST_BIG_2KW_1 t set ADDRESS=function(xxx,xx) where rowid between :start_id and :end_id’,language_flag=>dbms_sql.native,parallel_level => 50);

select count(1) from test.CUST_BIG_2KW_1 where rowid between ‘AAAVsyAAXAAAycAAAA’ and ‘AAAVsyAAXAAAy5/CcP’;

查询task状态：
SELECT task_name,status from user_parallel_execute_tasks where task_name=‘my_task’;

– exec dbms_parallel_execute.task_status(my_taskk’);
– exec DBMS_PARALLEL_EXECUTE.ADM_TASK_STATUS(table_owner => ‘test’ ,task_name => ‘my_task’)

6.删除chunks
exec dbms_parallel_execute.drop_chunks(‘my_task’);

7.删除task
exec dbms_parallel_execute.drop_task(‘my_task’);

8.task 有异常可以执行下面
exec DBMS_PARALLEL_EXECUTE.resume_task(‘my_task’);

查询执行job 状态：
select job_name,comments from dba_scheduler_jobs;
select * from dba_scheduler_jobs where owner =‘test’

9. 注意： 需要job （新增，修改，执行，删除）权限
需要scheduler 相关权限
很多时候没有并发很多可能是由于数据库 job_queue_processes 相关参数限制，需要调整到1000