Oracle Internals
JulianDyke.com

Welcome

Consultancy

Seminars

Blog

Presentations

Diagnostics

Internals

 

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
AND-EQUAL
BITMAP AND
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
BITMAP MERGE
BITMAP MINUS
BITMAP OR
BUFFER (SORT)
COLLECTION ITERATOR (CONSTRUCTOR FETCH)
COLLECTION ITERATOR (PICKLER FETCH)
COLLECTION ITERATOR (SUBQUERY FETCH)
CONCATENATION
CONNECT BY
CONNECT BY (WITH FILTERING)
CONNECT BY (WITHOUT FILTERING)
CONNECT BY (PUMP)
COUNT
COUNT (STOPKEY)
CREATE AS SELECT
CREATE INDEX STATEMENT
CREATE TABLE STATEMENT
DDL STATEMENT
DELETE
DELETE STATEMENT
DELETE STATEMENT (REMOTE)
DOMAIN INDEX
EXTERNAL TABLE ACCESS (FULL)
EXTERNAL TABLE ACCESS (SAMPLE)
FAST DUAL
FIC ENUMERATE FEED
FIC LOAD BITMAPS
FIC LOAD ITEMSETS
FIC RECURSIVE ITERATION
FILTER
FIRST ROW
FIXED TABLE (FIXED INDEX)
FIXED TABLE (FIXED INDEX #1)
FIXED TABLE (FIXED INDEX #2)
FIXED TABLE (FIXED INDEX #3)
FIXED TABLE (FULL)
FOR UPDATE
FREQUENT ITEMSET COUNTING
GENERATE (CUBE)
GRANULE ITERATOR
GRANULE ITERATOR (EXTERNAL CHUNK)
GRANULE ITERATOR (PARTITION)
GRANULE ITERATOR (ROWID RANGE)
HASH (GROUP BY)
HASH JOIN
HASH JOIN (ANTI)
HASH JOIN (BUFFERED)
HASH JOIN (OUTER)
HASH JOIN (SEMI)
HASH (UNIQUE)
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)
INTERSECTION
INTO
LOAD AS SELECT
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
MERGE JOIN
MERGE JOIN (ANTI)
MERGE JOIN (CARTESIAN)
MERGE JOIN (OUTER)
MERGE JOIN (SEMI)
MERGE STATEMENT
MERGE STATEMENT (REMOTE)
MINUS
MULTI-TABLE INSERT
NESTED LOOPS
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 COORDINATOR
PX PARTITION HASH (ALL)
PX PARTITION LIST (ALL)
PX PARTITION RANGE (ALL)
PX RECEIVE
PX SEND (BROADCAST)
PX SEND (BROADCAST LOCAL)
PX SEND (HASH)
PX SEND (PARTITION (KEY))
PX SEND (QC (ORDER))
PX SEND (QC (RANDOM))
PX SEND (RANGE)
RECURSIVE EXECUTION
REFERENCE MODEL
REMOTE
SELECT STATEMENT
SELECT STATEMENT (REMOTE)
SEQUENCE
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 (JOIN)
SORT (ORDER BY)
SORT (ORDER BY STOPKEY)
SORT (PARTITION JOIN)
SORT (UNIQUE)
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)
TABLE QUEUE
TEMP TABLE GENERATION
TEMP TABLE TRANSFORMATION
UNION-ALL
UNION-ALL (PARTITION)
UNION-ALL (PUSHED PREDICATE)
UPDATE
UPDATE STATEMENT
UPDATE STATEMENT (REMOTE)
VIEW
VIEW PUSHED PREDICATE
WINDOW (BUFFER)
WINDOW (BUFFER PUSHED RANK)
WINDOW (IN SQL MODEL) (SORT)
WINDOW (NOSORT)
WINDOW (NOSORT STOPKEY)
WINDOW (SORT)
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)