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