Database Reference
In-Depth Information
A rebuild of an index leads to a very similar execution plan (notice that according to operation 8, the data is
extracted from the index, not from the table):
ALTER INDEX i1 REBUILD PARALLEL 4
-----------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | Q1,01 | P->S | QC (ORDER) |
| 3 | INDEX BUILD NON UNIQUE | I1 | Q1,01 | PCWP | |
| 4 | SORT CREATE INDEX | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | Q1,01 | PCWP | |
| 6 | PX SEND RANGE | :TQ10000 | Q1,00 | P->P | RANGE |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 8 | INDEX FAST FULL SCAN| I1 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------
Creation and Validation of Constraints
When constraints (such as foreign keys and check constraints) are created or validated, the data already stored in the
table must be validated. For that purpose, the database engine executes a recursive query. For example, let's say that
you execute the following SQL statement:
ALTER TABLE t ADD CONSTRAINT t_id_nn CHECK (id IS NOT NULL)
Recursively, the database engine executes a query like the following one to validate the data stored in the table
(note that if the query returns no row, the data is valid):
SELECT rowid
FROM t
WHERE NOT (id IS NOT NULL)
As a result, if the table which the constraint is created on has a degree of parallelism of 2 or higher, the database
engine executes the query in parallel.
■
The degree of parallelism defined at the table level is used for the recursive query, regardless of whether at
the session level the parallel queries and ddL statements are enabled, forced, or disabled. In other words, the
ALTER SESSION ... PARALLEL
statement has no influence on the recursive query.
Note
When you define a primary key constraint, the database engine can't create the index in parallel. To avoid
this limitation, you have to create the (unique) index before defining the constraint. The following SQL statements
illustrate:
CREATE UNIQUE index t_pk ON t (id) PARALLEL 2
