Database Reference
In-Depth Information
•
Parallelization of both operations
: The slave processes scan table
t1
in parallel based on
block range granules and insert the data they get in the target table directly, without sending
the rows to another parallel slave set. Two important things should be highlighted. First, the
query coordinator isn't directly involved in the processing of data. Second, the data isn't
sent through a table queue (except for a negligible amount of information sent to the query
coordinator by operation 2, no communication takes place):
CREATE TABLE t2 PARALLEL 2 AS SELECT /*+ parallel(t1 2) */ * FROM t1
------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T2 | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
------------------------------------------------------------------------
The preceding examples show hints using the object-level syntax. To enable the parallelization of both operations
(the last example), from version 11.2 onward, for CTAS statements it's possible to use the statement-level syntax, as
shown in the following example:
CREATE
/*+ parallel(2) */
TABLE t2 AS SELECT * FROM t1
An important difference of using this syntax, compared to the previous one, is that because the
PARALLEL
clause
isn't specified, the degree of parallelism is used only during the creation of the table. In other words, in the data
dictionary, a degree of parallelism of 1 is associated to the table.
Creation and Rebuild of Indexes
You can create and rebuild indexes in parallel. To do this, two sets of slave processes work together. The first set reads
the data to be indexed. The second set sorts the data it receives from the first set and builds the index. The following
SQL statement is an example. Notice how the first set executes operations 6 through 8 (Q1,00) and the second
executes operations 2 through 5 (
Q1,01
). Data is distributed between the two sets using the range method (so that
each parallel slave of the second set works on its slice of the index) and a parallel to parallel (
P->P
) relationship:
CREATE INDEX i1 ON t1 (id) PARALLEL 4
--------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
| 0 | CREATE 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 | |
