GRANULE ITERATOR (PARTITION)

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

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 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'