Oracle Internals

JulianDyke.com

Acknowledgements

Operations

An execution plan consists of a number of OPERATIONS (steps). Operations may have OPTIONS. Options modify the behaviour of the operation.

This website describes all operations/options which have been observed in the Oracle 7.3.4, 8.0.4, 8.0.5, 8.0.6, 8.1.5, 8.1.6, 8.1.7, 9.0.1, 9.2.0, 10.1.0 and 10.2.0.

Where possible one or more basic examples are included to demonstrate the behaviour required to generate the operation

ALTER INDEX STATEMENT

BITMAP COMPACTION

BITMAP CONSTRUCTION

BITMAP CONVERSION (COUNT)

BITMAP CONVERSION (FROM ROWIDS)

BITMAP CONVERSION (TO ROWIDS)

BITMAP INDEX (FAST FULL SCAN)

BITMAP INDEX (FULL SCAN)

BITMAP INDEX (RANGE SCAN)

BITMAP INDEX (SINGLE VALUE)

BITMAP JOIN INDEX UPDATE

BITMAP JOIN INDEX UPDATE STATEMENT

BITMAP KEY ITERATION

COLLECTION ITERATOR (CONSTRUCTOR FETCH)

COLLECTION ITERATOR (PICKLER FETCH)

COLLECTION ITERATOR (SUBQUERY FETCH)

CONNECT BY (WITH FILTERING)

CONNECT BY (WITHOUT FILTERING)

CONNECT BY (PUMP)

COUNT (STOPKEY)

CREATE AS SELECT

CREATE INDEX STATEMENT

CREATE TABLE STATEMENT

DELETE STATEMENT

DELETE STATEMENT (REMOTE)

EXTERNAL TABLE ACCESS (FULL)

EXTERNAL TABLE ACCESS (SAMPLE)

FIC ENUMERATE FEED

FIC LOAD BITMAPS

FIC LOAD ITEMSETS

FIC RECURSIVE ITERATION

FIXED TABLE (FIXED INDEX)

FIXED TABLE (FIXED INDEX #1)

FIXED TABLE (FIXED INDEX #2)

FIXED TABLE (FIXED INDEX #3)

FIXED TABLE (FULL)

FREQUENT ITEMSET COUNTING

GENERATE (CUBE)

GRANULE ITERATOR

GRANULE ITERATOR (EXTERNAL CHUNK)

GRANULE ITERATOR (PARTITION)

GRANULE ITERATOR (ROWID RANGE)

HASH (GROUP BY)

HASH JOIN (ANTI)

HASH JOIN (BUFFERED)

HASH JOIN (OUTER)

HASH JOIN (SEMI)

INDEX (FAST FULL SCAN)

INDEX (FULL SCAN)

INDEX (FULL SCAN DESCENDING)

INDEX (FULL SCAN (MIN/MAX))

INDEX (RANGE SCAN)

INDEX (RANGE SCAN DESCENDING)

INDEX (RANGE SCAN (MIN/MAX))

INDEX (SAMPLE FAST FULL SCAN)

INDEX (SKIP SCAN)

INDEX (SKIP SCAN DESCENDING)

INDEX (UNIQUE SCAN)

INDEX BUILD (NON UNIQUE)

INDEX BUILD (NON UNIQUE (LOCAL))

INDEX BUILD (UNIQUE)

INDEX BUILD (UNIQUE (LOCAL))

INLIST ITERATOR (CONCATENATED)

INLIST ITERATOR

INSERT STATEMENT

INSERT STATEMENT (REMOTE)

MAT_VIEW ACCESS (BY INDEX ROWID)

MAT_VIEW ACCESS (BY ROWID RANGE)

MAT_VIEW ACCESS (BY USER ROWID)

MAT_VIEW ACCESS (FULL)

MAT_VIEW ACCESS (SAMPLE)

MAT_VIEW ACCESS (SAMPLE BY ROWID RANGE)

MAT_VIEW REWRITE ACCESS (FULL)

MERGE JOIN (ANTI)

MERGE JOIN (CARTESIAN)

MERGE JOIN (OUTER)

MERGE JOIN (SEMI)

MERGE STATEMENT

MERGE STATEMENT (REMOTE)

MULTI-TABLE INSERT

NESTED LOOPS (ANTI)

NESTED LOOPS (OUTER)

NESTED LOOPS (PARTITION OUTER)

NESTED LOOPS (SEMI)

PARTITION (CONCATENATED)

PARTITION (EMPTY)

PARTITION (SINGLE)

PARTITION HASH (ALL)

PARTITION HASH (EMPTY)

PARTITION HASH (INLIST)

PARTITION HASH (ITERATOR)

PARTITION HASH (SINGLE)

PARTITION LIST (ALL)

PARTITION LIST (EMPTY)

PARTITION LIST (INLIST)

PARTITION LIST (ITERATOR)

PARTITION LIST (OR)

PARTITION LIST (SINGLE)

PARTITION LIST (SUBQUERY)

PARTITION RANGE (ALL)

PARTITION RANGE (EMPTY)

PARTITION RANGE (INLIST)

PARTITION RANGE (ITERATOR)

PARTITION RANGE (OR)

PARTITION RANGE (SINGLE)

PARTITION RANGE (SUBQUERY)

PX BLOCK ITERATOR

PX PARTITION HASH (ALL)

PX PARTITION LIST (ALL)

PX PARTITION RANGE (ALL)

PX SEND (BROADCAST)

PX SEND (BROADCAST LOCAL)

PX SEND (PARTITION (KEY))

PX SEND (QC (ORDER))

PX SEND (QC (RANDOM))

PX SEND (RANGE)

RECURSIVE EXECUTION

REFERENCE MODEL

SELECT STATEMENT

SELECT STATEMENT (REMOTE)

SEQUENCE (REMOTE)

SORT (AGGREGATE)

SORT (CREATE INDEX)

SORT (GROUP BY)

SORT (GROUP BY NOSORT)

SORT (GROUP BY NOSORT ROLLUP)

SORT (GROUP BY ROLLUP)

SORT (GROUP BY STOPKEY)

SORT (ORDER BY)

SORT (ORDER BY STOPKEY)

SORT (PARTITION JOIN)

SORT (UNIQUE NOSORT)

SORT (UNIQUE STOPKEY)

SQL MODEL (ACYCLIC)

SQL MODEL (ACYCLIC FAST)

SQL MODEL (ORDERED)

SQL MODEL (ORDERED FAST)

TABLE ACCESS (BY GLOBAL INDEX ROWID)

TABLE ACCESS (BY INDEX ROWID)

TABLE ACCESS (BY LOCAL INDEX ROWID)

TABLE ACCESS (BY ROWID)

TABLE ACCESS (BY ROWID RANGE)

TABLE ACCESS (BY USER ROWID)

TABLE ACCESS (CLUSTER)

TABLE ACCESS (FULL)

TABLE ACCESS (HASH)

TABLE ACCESS (SAMPLE)

TABLE ACCESS (SAMPLE BY ROWID RANGE)

TEMP TABLE GENERATION

TEMP TABLE TRANSFORMATION

UNION-ALL (PARTITION)

UNION-ALL (PUSHED PREDICATE)

UPDATE STATEMENT

UPDATE STATEMENT (REMOTE)

VIEW PUSHED PREDICATE

WINDOW (BUFFER)

WINDOW (BUFFER PUSHED RANK)

WINDOW (IN SQL MODEL) (SORT)

WINDOW (NOSORT)

WINDOW (NOSORT STOPKEY)

WINDOW (SORT PUSHED RANK)

The following operations are believed to exist in one or more versions, but have never been observed

FIFO BUFFER

PARTITION COMBINED (ALL)

PARTITION COMBINED (EMPTY)

PARTITION COMBINED (INLIST)

PARTITION COMBINED (ITERATOR)

PARTITION COMBINED (SINGLE)

PARTITION SYSTEM (ALL)

PARTITION SYSTEM (EMPTY)

PARTITION SYSTEM (INLIST)

PARTITION SYSTEM (ITERATOR)

PARTITION SYSTEM (SINGLE)

VIEW HIERARCHY

WINDOW (CHILD)

WINDOW (CHILD PUSHED RANK)