Study of Parallel DML ( Insert into ... Select)
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来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/638844/viewspace-773269/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
