Database Reference
In-Depth Information
As you can see, there is more happening here than when we simply queried the table in parallel. We have
17 processes working on this operation, not just 9 as before. This is because the plan that was developed includes a
step to update the table and independent steps to update the index entries. Look at a
BASIC
plus
PARALLEL
-enabled
explain plan output from
DBMS_XPLAN
:
EODA@ORA12CR1> explain plan for update big_table set status = 'done';
Explained.
EODA@ORA12CR1> select * from table(dbms_xplan.display(null,null,'BASIC +PARALLEL'));
We see the following:
---------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 3 | INDEX MAINTENANCE | BIG_TABLE | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE |
| 6 | UPDATE | BIG_TABLE | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| BIG_TABLE | Q1,00 | PCWP | |
---------------------------------------------------------------------------
As a result of the pseudo-distributed implementation of PDML, certain limitations are associated with it:
•
Triggers are not supported during a PDML operation. This is a reasonable limitation in my
opinion, since triggers tend to add a large amount of overhead to the update, and you are
using PDML to go fast—the two features don't go together.
•
There are certain declarative RI constraints that are not supported during the PDML, since
each slice of the table is modified as a separate transaction in the separate session. Self-
referential integrity is not supported, for example. Consider the deadlocks and other locking
issues that would occur if it were supported.
•
You cannot access the table you've modified with PDML until you commit or roll back.
•
Advanced replication is not supported with PDML (because the implementation of advanced
replication is trigger-based).
•
Deferred constraints (i.e., constraints that are in the deferred mode) are not supported.
•
PDML may only be performed on tables that have bitmap indexes or LOB columns if the table
is partitioned, and then the degree of parallelism would be capped at the number of partitions.
You cannot parallelize an operation within partitions in this case, as each partition would get
a single parallel execution server to operate on it. We should note that starting with Oracle 12
c
,
you can run PDML on SecureFiles LOBs without partitioning.
•
Distributed transactions are not supported when performing PDML.
•
Clustered tables are not supported with PDML.
If you violate any of those restrictions, one of two things will happen: either the statement will be performed
serially (no parallelism will be involved) or an error will be raised. For example, if you already performed the PDML
against table
T
and then attempted to query table
T
before ending your transaction, then you will receive the error
ORA-12838: cannot read/modify an object after modifying it in parallel
.
