実行計画の OPERATION と OPTION は X$XPLTON と X$XPLTOO に

実行計画の OPERATION ってどこのX$表に入っているんだろうと思って調べたら、tech.E2SN に書かれていた。

  • X$XPLTON (eXPLain rowsource Operator Names) (11.1 -)
  • X$XPLTOO (eXPLain rowsource Operator Options) (11.1 -)
  • DBA_HIST_PLAN_OPERATION_NAME (11.2 -)
  • DBA_HIST_PLAN_OPTION_NAME (11.2 -)

Every Oracle execution plan is logically a tree of row source operators (row sources)

A rowsource operator (sometimes called rowsource operation, execution plan operation or just an execution plan line) is:

  1. Physically just a C function
  2. ...which calls its child rowsource functions in the execution plan tree
  3. ...fetches rows returned from the child rowsources (functions)
  4. ...processes the rows (depending on what kind of rowsource it is)
  5. ...passes the rows up the tree to parent rowsources
  6. ...once the rows reach the root in the execution plan tree, they are returned back to fetch call which sends them +back to the client (in case of a SELECT statement). In case of DML statements, the root rowsource function is the one which performs the required changes on the rows returned to the tree (update, delete).

...

Many operators also have options which affect how exactly the operator works. For example INDEX operator has options such as UNIQUE, RANGE SCAN, SKIP SCAN etc, which determine the way the INDEX operator internally works.

Starting from Oracle 11.1 it is possible to list all (non-hidden) rowsources available by querying X$XPLTON (eXPLain rowsource Operator Names).
The rowsource operator options are listed in X$XPLTOO (eXPLain rowsource Operator Options). Starting from Oracle 11.2, the same data can be queried from AWR views DBA_HIST_PLAN_OPERATION_NAME and DBA_HIST_PLAN_OPTION_NAME.

  • Oracle Rowsource Operator Name list
BITMAP KEY ITERATION
NESTED LOOPS
MERGE JOIN
HASH JOIN
AND-EQUAL
BITMAP AND
INTERSECTION
MINUS
BITMAP MINUS
GENERATE
BITMAP CONVERSION
BITMAP INDEX
CONNECT BY
CONNECT BY PUMP
COUNT
DELETE
UPDATE
FIRST ROW
FILTER
FOR UPDATE
FIXED TABLE
INLIST ITERATOR
INDEX
INDEX BUILD
LOAD AS SELECT
MULTI-TABLE INSERT
INTO
DIRECT LOAD INTO
COLLECTION ITERATOR
DOMAIN INDEX
PARTITION RANGE
PARTITION HASH
PARTITION LIST
PARTITION SYSTEM
PARTITION COMBINED
GRANULE ITERATOR
SEQUENCE
TABLE ACCESS
TABLE QUEUE
INDEX MAINTENANCE
FIFO BUFFER
WINDOW
TEMP TABLE TRANSFORMATION
SORT
BUFFER
BITMAP COMPACTION
BITMAP CONSTRUCTION
BITMAP OR
BITMAP MERGE
CONCATENATION
UNION-ALL
VIEW PUSHED PREDICATE
VIEW
REMOTE
SELECT STATEMENT
INSERT STATEMENT
UPDATE STATEMENT
MERGE STATEMENT
DELETE STATEMENT
CREATE TABLE STATEMENT
ALTER INDEX STATEMENT
CREATE INDEX STATEMENT
DDL STATEMENT
TEMP TABLE GENERATION
TRUNCATE TEMP TABLE
RECURSIVE EXECUTION
MERGE
EXTERNAL TABLE ACCESS
BITMAP JOIN INDEX UPDATE
BITMAP JOIN INDEX UPDATE STATEMENT
VIEW HIERARCHY
SQL MODEL
MAT_VIEW ACCESS
MAT_VIEW REWRITE ACCESS
FREQUENT ITEMSET COUNTING
FIC ENUMERATE FEED
FIC LOAD BITMAPS
FIC LOAD ITEMSETS
FIC DETECT END
FIC RECURSIVE ITERATION
DOMAIN INDEX BUILD
DOMAIN INDEX PARTITION BUILD
FAST DUAL
REFERENCE MODEL
PX RECEIVE
QC SEND
PX SEND
PX COORDINATOR
PX BLOCK
PX PARTITION RANGE
PX PARTITION HASH
PX PARTITION LIST
WINDOW (IN SQL MODEL)
UNION ALL PUSHED PREDICATE
DST ENUMERATE FEED
DST DETECT END
DST RECURSIVE ITERATION
DST SPLIT
DECISION TREE CLASSIFICATION
DST PRUNE
JOIN FILTER
HASH
ERROR LOGGING
LOAD TABLE CONVENTIONAL
PART JOIN FILTER
AW SCAN
PARTITION REFERENCE
PX PARTITION REFERENCE
XPATH EVALUATION
RESULT CACHE
DM FEED PREDICTOR-TARGET PAIRS
DM SUPERVISED BINNING
DM HASH-BROADCAST COUNTS
BULK BINDS GET
APPROXIMATE NDV
UNPIVOT
TRANSPOSE
MONITORING
CUBE SCAN
JOINED CUBE SCAN
CUBE ACCESS
MAT_VIEW CUBE ACCESS
MAT_VIEW REWRITE CUBE ACCESS
CUBE TABLE SCAN
JOINED CUBE TABLE SCAN
CUBE TABLE ACCESS
UNION ALL (RECURSIVE WITH)
RECURSIVE WITH PUMP
  • Oracle Rowsource Operator Options list
OUTER
ANTI
SEMI
CARTESIAN
TO ROWIDS
FROM ROWIDS
COUNT
SINGLE VALUE
SAMPLE FAST FULL SCAN
FAST FULL SCAN
FULL SCAN
RANGE SCAN
UNIQUE SCAN
SKIP SCAN
FULL SCAN (MIN/MAX)
RANGE SCAN (MIN/MAX)
FULL SCAN DESCENDING
RANGE SCAN DESCENDING
SKIP SCAN DESCENDING
WITH FILTERING
WITHOUT FILTERING
STOPKEY
FIXED INDEX
FULL
HASH
INDEX BUILD
UNIQUE (LOCAL)
NON UNIQUE (LOCAL)
UNIQUE
NON UNIQUE
PICKLER FETCH
CONSTRUCTOR FETCH
SUBQUERY FETCH
REMOTE
SAMPLE
SAMPLE BY ROWID RANGE
BY ROWID RANGE
BY LOCAL INDEX ROWID
BY GLOBAL INDEX ROWID
BY INDEX ROWID
BY USER ROWID
CLUSTER
NOSORT
BUFFER PUSHED RANK
BUFFER
CHILD PUSHED RANK
CHILD
SORT PUSHED RANK
SINGLE
EMPTY
INLIST
ALL
ITERATOR
PARTITION
AGGREGATE
GROUP BY STOPKEY
GROUP BY
UNIQUE NOSORT
GROUP BY NOSORT
GROUP BY ROLLUP
CUBE
ROLLUP
SORT
CREATE INDEX
UNIQUE STOPKEY
ORDER BY STOPKEY
ORDER BY
JOIN
GROUP BY NOSORT ROLLUP
CUSTOM OPTION
ROWID RANGE
EXTERNAL CHUNK
ACYCLIC
CYCLIC
ACYCLIC FAST
ORDERED
ORDERED FAST
RIGHT OUTER
RIGHT ANTI
RIGHT SEMI
PARTITION OUTER
(LOCAL)
PARTITION (ROWID)
PARTITION (KEY)
RANGE
ROUND-ROBIN
BROADCAST
QC (ORDER)
QC (RANDOM)
BROADCAST LOCAL
HASH LOCAL
HYBRID (ROWID PKEY)
RANDOM LOCAL
HASH (BLOCK ADDRESS)
MIXED HASH BCAST LOCAL
MIXED HASH BCAST
OUTER BUFFERED
RIGHT OUTER BUFFERED
ANTI BUFFERED
RIGHT ANTI BUFFERED
SEMI BUFFERED
RIGHT SEMI BUFFERED
CARTESIAN BUFFERED
PARTITION OUTER BUFFERED
HASH ALL
RANGE ALL
LIST
LIST ALL
SYSTEM
FORCED SERIAL
PARTITION JOIN
AW HASH
CREATE
USE
SUBQUERY
OR
MULTI-COLUMN
NOSORT STOPKEY
BUFFERED
FULL OUTER
FULL OUTER BUFFERED
NO FILTERING WITH START-WITH
JOIN-FILTER
ANTI NA
ANTI SNA
ANTI NA BUFFERED
ANTI SNA BUFFERED
RIGHT ANTI NA
RIGHT ANTI SNA
RIGHT ANTI NA BUFFERED
RIGHT ANTI SNA BUFFERED
GROUP BY PIVOT
GROUP BY NOSORT PIVOT
DUMP
VERIFY
STRIP
STORAGE FULL
STORAGE SAMPLE
STORAGE SAMPLE BY ROWID RANGE
STORAGE BY ROWID RANGE
PARTIAL OUTER
OPTIMIZED
SPECIFIED
STORAGE SAMPLE FAST FULL SCAN
STORAGE FAST FULL SCAN
STORAGE FULL SCAN
STORAGE RANGE SCAN
STORAGE FULL SCAN (MIN/MAX)
WITH FILTERING (UNIQUE)
WITHOUT FILTERING (UNIQUE)
NO FILTERING WITH SW (UNIQUE)
BREADTH FIRST
DEPTH FIRST
AND
Oracle Execution Plan Row Source Operator Reference - tech.E2SN