実行計画の 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:
- Physically just a C function
- ...which calls its child rowsource functions in the execution plan tree
- ...fetches rows returned from the child rowsources (functions)
- ...processes the rows (depending on what kind of rowsource it is)
- ...passes the rows up the tree to parent rowsources
- ...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 ANDOracle Execution Plan Row Source Operator Reference - tech.E2SN
