Beyond Data Access and Join Optimization - Troubleshooting Oracle Performance - page 631

| 6 | PX BLOCK ITERATOR | | 2 | 100K| 2767 | 2464 |
|* 7 | TABLE ACCESS FULL | T | 26 | 100K| 2767 | 2464 |
| 8 | PX PARTITION HASH ALL | | 2 | 100K| 2719 | 2464 |
| 9 | TABLE ACCESS FULL | T | 2 | 100K| 2719 | 2464 |
-------------------------------------------------------------------------------------
The session executing a parallel DML statement (and only that session—for other sessions, the uncommitted
data isn't even visible) can't access the modified table without committing (or rolling back) the transaction. SQL
statements executed before committing (or rolling back) terminate with an ORA-12838: cannot read/modify an
object after modifying it in parallel error. Here's an example (note that the UPDATE statement is parallelized):
SQL> UPDATE t SET id = id + 1;
SQL> SELECT count(*) FROM t;
SELECT count(*) FROM t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> COMMIT;
SQL> SELECT count(*) FROM t;
COUNT(*)
----------
100000
A restriction similar to the preceding one, is when a parallel DML statement, which is attempting to modify an
object that was previously modified with a serial DML statement, raises an ORA-12839: cannot modify an object
in parallel after modifying it error. Here's an example (note that a SELECT FOR UPDATE , to set the row lock, has
to modify the table):
SQL> SELECT id FROM t WHERE rownum = 1 FOR UPDATE;
ID
----------
2343
SQL> UPDATE t SET id = id + 1;
UPDATE t SET id = id + 1
*
ERROR at line 1:
ORA-12839: cannot modify an object in parallel after modifying it
SQL> COMMIT;
SQL> UPDATE t SET id = id + 1;
100000 rows updated.