Transaction restrictions for Parallel DML on 11g

This will be just short post about restriction on parallel DML that I wasn't aware of.

Last week we had to perform quick update of several million rows in very big table. Parallel DML has proven to be the best tool to speed up large DML operation.

A DML can be parallelized only if you explicitly enable parallel DML in session level. This step is important because parallel and serial DML have different locking, transaction and space requirements.

I'd like to draw attention on one transaction restriction when using parallel DML. You can find other restrictions in Oracle Documentation.

This part was interesting to me:

If any DML statement in a transaction modifies a table in parallel, no subsequent serial or parallel query or DML statement can access the same table again in that transaction. This means that the results of parallel modifications cannot be seen during the transaction.

Even if PL/SQL procedure or block is executed in parallel DML session, this rule applies also.

Test is performed on 11.1.0.7 and 11.2.0.3 databases on Linux machine.

SQL> alter session enable parallel dml;

Session altered.

SQL> update /*+parallel(bt,8) */ big_table bt
  2  set bt.data_object_id=bt.data_object_id*100;

1000000 rows updated.

SQL> select count(*) from big_table;
select count(*) from big_table
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> commit;

Commit complete.

SQL> select count(*) from big_table;

  COUNT(*)
----------
   1000000

1 row selected.

PL/SQL

SQL> declare
  2     l_cnt number := 0;
  3  begin
  4     execute immediate 'alter session enable parallel dml';
  5     update /*+parallel(bt,8) */ big_table bt
  6     set bt.data_object_id=bt.data_object_id*100;
  7
  8     select count(*) into l_cnt
  9     from big_table;
10  end;
11  /
declare
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
ORA-06512: at line 8

So I cannot query modified table (from my session) until transaction is closed.
Nice to know that.

Parallel DML is a nice feature. It is mostly used in Data Warehouse and for running batch jobs in OLTP databases. But due to it's restrictions and different behavior then serial DML it is advisable to check documentation before using it.

References: http://docs.oracle.com/cd/B28359_01/server.111/b28313/usingpe.htm#CACEJACE