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'