Appears in a parallel execution plan
This operation was introduced in Oracle 9.2.0. Replaces GRANULE ITERATOR in Oracle 9.0.1 and below
This operation is implemented in the following versions
|
This operation was replaced in Oracle 10.1 by PX BLOCK (ITERATOR)
This operation appears in the slave process of a parallel query. The execution plan is only generated if the query is executed, therefore it is not sufficient just to explain the query
The execution plan can be observed in V$SQL_PLAN
This example was developed using Oracle 9.2.0
This example requires the following objects
CREATE TABLE t1 ( c1 NUMBER NOT NULL, c2 NUMBER, c3 NUMBER, c4 CHAR(100) ) PARTITION BY RANGE (c1) ( PARTITION p1 VALUES LESS THAN (10000), PARTITION p2 VALUES LESS THAN (20000), PARTITION p3 VALUES LESS THAN (30000), PARTITION p4 VALUES LESS THAN (40000) ); BEGIN FOR f IN 0..39999 LOOP INSERT INTO t1 VALUES (f,MOD (f,1000),TRUNC (f/10),'X'); END LOOP; END; / COMMIT; CREATE INDEX i1 ON t1 (c2) LOCAL; ANALYZE TABLE t1 COMPUTE STATISTICS;
The statement
SELECT /*+ PARALLEL (t1,4) */ c1,COUNT(*) FROM t1 WHERE c2 < 2 AND c3 < 10 GROUP BY c1;
generates the following execution plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT* (GROUP BY) 2 1 SORT* (GROUP BY) 3 2 PARTITION RANGE* (ALL) 4 3 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'T1' 5 4 INDEX* (RANGE SCAN) OF 'I1'
Step 2 executes the following SQL statement using slave processes
SELECT /*+ PIV_GB */ A1.C1 C0,SYS_OP_MSR(COUNT(*)) C1 FROM ( SELECT /*+ NO_EXPAND INDEX(A2 "I1") */ A2.ROWID C0,A2."C2" C1 FROM "USER1"."T1" PX_GRANULE(0, PARTITION, DYNAMIC) A2 WHERE A2."C2"<2 AND A2."C3"< 10 ) A1 GROUP BY A1.C1;
This generates the following execution plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 GRANULE ITERATOR (PARTITION) 3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T1' 4 3 INDEX (RANGE SCAN) OF 'I1'