GRANULE ITERATOR (EXTERNAL CHUNK)

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 a directory

  CREATE DIRECTORY dir AS "pathname";

The directory does not need to be added to the utl_file_dir parameter

Create a file within the directory e.g.

  DECLARE 
    l_handle UTL_FILE.FILE_TYPE;
    l_line   VARCHAR2(200);
  BEGIN
    l_handle := utl_file.fopen ('DIR1','TEST1.CSV','W');

    FOR f IN 0..9999
    LOOP
      l_line := TO_CHAR (f)||','||LPAD (TO_CHAR (f),10,'0');
      utl_file.put_line (l_handle,l_line);
    END LOOP;
    
    utl_file.fclose (l_handle);
  END;
  /

Create a table definition

    CREATE TABLE t1 
    (
      c1 NUMBER,
      c2 VARCHAR2(10)
    )
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY dir1
      ACCESS PARAMETERS
      (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        (    
          c1 CHAR,
          c2 CHAR
        )
      )
      LOCATION ('t1.csv')
    );

The statement

    SELECT /*+ PARALLEL (t1,2) */ COUNT(*) 
    FROM t1;

generates the following execution plan

    0     SELECT STATEMENT Optimizer=CHOOSE
    1   0   SORT (AGGREGATE)
    2   1     SORT* (AGGREGATE)
    3   2       EXTERNAL TABLE ACCESS* (FULL) OF 'T1'

Step 2 executes the following SQL statement using slave processes

  SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*))
  FROM 
  (
    SELECT /*+ NO_EXPAND FULL(A2) */ 0
    FROM "USER1"."T1" PX_GRANULE(0, CHUNK, DYNAMIC) A2
  ) A1;

This generates the following execution plan

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