GRANULE ITERATOR (ROWID RANGE)

Description

Appears in a parallel execution plan

Versions

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

9.2.0

This operation was replaced in Oracle 10.1 by PX BLOCK (ITERATOR)

Example

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 definitions 

  CREATE TABLE t1 (c1 NUMBER);

  INSERT INTO t1 VALUES (0);

The statement 

    SELECT /*+ PARALLEL (t1,4) */ c1,COUNT(*) 
    FROM t1
    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       TABLE ACCESS (FULL) OF 'T1'

Step 2 executes the following SQL statement using slave processes 

  SELECT /*+ PIV_GB */ A1.C0 C0,SYS_OP_MSR(COUNT(*)) C1
  FROM 
  (
    SELECT /*+ NO_EXPAND ROWID(A2) */ A2."C1" C0
    FROM "USER1"."T1" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A2
  ) A1
  GROUP BY A1.C0

This generates the following execution plan 

    0     SELECT STATEMENT Optimizer=CHOOSE
    1   0   SORT (GROUP BY)
    2   1     GRANULE ITERATOR (ROWID RANGE)
    3   2       TABLE ACCESS (BY ROWID RANGE) OF 'T1'