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'