Parallel execution - send rows to parent process based on partition key
This operation is implemented in the following versions
|
This example was developed using Oracle 10.2.0.1 on Linux
This example requires the following table definitions
CREATE TABLE t1 ( c1 NUMBER, c2 NUMBER, c3 NUMBER, c4 CHAR(100) ) PARTITION BY RANGE (c1) ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30), PARTITION p4 VALUES LESS THAN (40) ); CREATE TABLE t2 ( c1 NUMBER, c2 NUMBER, c3 NUMBER, c4 CHAR(100) ) PARTITION BY RANGE (c1) ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30), PARTITION p4 VALUES LESS THAN (40) );
Dynamic sampling must be disabled using
ALTER SESSION SET optimizer_dynamic_sampling = 0;
The statement
SELECT /*+ PARALLEL (t1,4) */ t2.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
generates the following execution plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 PX COORDINATOR 2 1 PX SEND (QC (RANDOM)) 3 2 HASH JOIN 4 3 PX PARTITION RANGE (ALL) 5 4 TABLE ACCESS (FULL) OF 'T1' 6 3 BUFFER (SORT) 7 6 PX RECEIVE 8 7 PX SEND (PARTITION (KEY)) 9 8 PARTITION RANGE (ALL) 10 9 TABLE ACCESS (FULL) OF 'T2'