ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Study of Parallel DML ( Insert into ... Select)

Study of Parallel DML ( Insert into ... Select)

原创 Linux操作系统 作者:chncaesar 时间:2013-09-24 04:33:40 0 删除 编辑
Here, parallel DML refers to insert, update, delete, merge, not select.

Rules of Parallel DML
1. alter session enable parallel DML --Must!

2.A transaction can contain multiple parallel DML statements that modify different tables, but after a parallel DML statement modifies a table, no subsequent serial or parallel statement (DML or query) can access the same table again in that transaction.
 
3. When you are inserting in parallel mode, direct-path INSERT is the default.

Experiments.
show rel;
release 110200300

1. Serial Conventional Load 
alter session disable parallel DML;
Insert /*+ PARALLEL(4) */ into /* hello1*/ orders select ROWNUM,ROWNUM,sysdate-mod((ROWNUM-1),31),ROWNUM FROM CUSTOMER; 
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%hello1%';

select count(*) from orders; --Successful
COUNT(*)
--------
10000

2. Parallel Direct-Path Load
rollback;
alter session enable parallel DML;
Insert /*+ PARALLEL(4) */ into /* hello2*/ orders select ROWNUM,ROWNUM,sysdate-mod((ROWNUM-1),31),ROWNUM FROM CUSTOMER; 
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%hello2%';
select count(*) from orders; 
--ORA-12839: Cannot read/modify an object after modifying it in parallel.

3. Parallel Conventional Load
rollback;
Insert /*+ NOAPPEND PARALLEL(4) */ into /* hello3*/ orders select ROWNUM,ROWNUM,sysdate-mod((ROWNUM-1),31),ROWNUM FROM CUSTOMER; 
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%hello3%';
select count(*) from orders; 
--ORA-12839: Cannot read/modify an object after modifying it in parallel.





1.jpg

2.jpg

3.jpg

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

下一篇: DDL Parameters
请登录后发表评论 登录
全部评论

注册时间:2013-07-30

  • 博文量
    102
  • 访问量
    902135