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