COLLECTION ITERATOR (SUBQUERY FETCH)

Description

Used with table functions

Versions

This operation was introduced in Oracle 8.1.5

This operation is implemented in the following versions

Example

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following type and function definitions

    CREATE OR REPLACE TYPE ty1 AS TABLE OF NUMBER;
    /
    CREATE TABLE t1 (c1 NUMBER);

The table does not need to be analysed

The statement

    SELECT * FROM TABLE (CAST (MULTISET
    (
	SELECT c1 FROM t1
	WHERE ROWNUM < 10
    )
    AS ty1));

generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   COLLECTION ITERATOR (SUBQUERY FETCH)
2   1     COUNT (STOPKEY)
3   2       TABLE ACCESS (FULL) OF 'T1'