The row source tree is the core of the execution plan. It contains the sequence of operations that the database performs to run the statement; each operation may have options associated with it. The operation in execution plan is also known as row source operator. If you look at the below example, TABLE ACCESS operation is performed on both plans, and the former has FULL and latter has SAMPLE. Each option will affect the way operation works – FULL retrieves all the rows from a table and SAMPLE retrieves sampled rows from a table.
SELECT * FROM t; Plan hash value: 2153619298 ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| T | ---------------------------------- SELECT * FROM t SAMPLE (5); Plan hash value: 3749081399 ------------------------------------ | Id | Operation | Name | ------------------------------------ | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS SAMPLE| T | ------------------------------------
Starting from 11g R1, it is possible to list all documented operations by querying X$XPLTON (Explain Plan Table Operation Names).
SQL> SELECT xplton_id operation_id, 2 xplton_name operation_name 3 FROM x$xplton; OPERATION_ID OPERATION_NAME ------------ ---------------------------------------- 0 1 BITMAP KEY ITERATION 2 NESTED LOOPS 3 MERGE JOIN 4 HASH JOIN 5 AND-EQUAL 6 BITMAP AND 7 INTERSECTION 8 MINUS 9 BITMAP MINUS 10 GENERATE 11 BITMAP CONVERSION 12 BITMAP INDEX 13 CONNECT BY 14 CONNECT BY PUMP 15 COUNT 16 DELETE 17 UPDATE 18 FIRST ROW 19 FILTER 20 FOR UPDATE 21 FIXED TABLE 22 INLIST ITERATOR 23 INDEX 24 INDEX BUILD 25 LOAD AS SELECT 26 MULTI-TABLE INSERT 27 INTO 28 DIRECT LOAD INTO 29 COLLECTION ITERATOR 30 DOMAIN INDEX 31 PARTITION RANGE 32 PARTITION HASH 33 PARTITION LIST 34 PARTITION SYSTEM 35 PARTITION COMBINED 36 GRANULE ITERATOR 37 SEQUENCE 38 TABLE ACCESS 39 TABLE QUEUE 40 INDEX MAINTENANCE 41 FIFO BUFFER 42 WINDOW 43 TEMP TABLE TRANSFORMATION 44 SORT 45 BUFFER 46 BITMAP COMPACTION 47 BITMAP CONSTRUCTION 48 BITMAP OR 49 BITMAP MERGE 50 CONCATENATION 51 UNION-ALL 52 VIEW PUSHED PREDICATE 53 VIEW 54 REMOTE 55 SELECT STATEMENT 56 INSERT STATEMENT 57 UPDATE STATEMENT 58 MERGE STATEMENT 59 DELETE STATEMENT 60 CREATE TABLE STATEMENT 61 ALTER INDEX STATEMENT 62 CREATE INDEX STATEMENT 63 DDL STATEMENT 64 Can't Explain!! 65 TEMP TABLE GENERATION 66 TRUNCATE TEMP TABLE 67 RECURSIVE EXECUTION 68 MERGE 69 EXTERNAL TABLE ACCESS 70 BITMAP JOIN INDEX UPDATE 71 BITMAP JOIN INDEX UPDATE STATEMENT 72 VIEW HIERARCHY 73 SQL MODEL 74 MAT_VIEW ACCESS 75 MAT_VIEW REWRITE ACCESS 76 FREQUENT ITEMSET COUNTING 77 FIC ENUMERATE FEED 78 FIC LOAD BITMAPS 79 FIC LOAD ITEMSETS 80 FIC DETECT END 81 FIC RECURSIVE ITERATION 82 DOMAIN INDEX BUILD 83 DOMAIN INDEX PARTITION BUILD 84 FAST DUAL 85 REFERENCE MODEL 86 PX RECEIVE 87 QC SEND 88 PX SEND 89 PX COORDINATOR 90 PX BLOCK 91 PX PARTITION RANGE 92 PX PARTITION HASH 93 PX PARTITION LIST 94 WINDOW (IN SQL MODEL) 95 UNION ALL PUSHED PREDICATE 96 DST ENUMERATE FEED 97 DST DETECT END 98 DST RECURSIVE ITERATION 99 DST SPLIT 100 DECISION TREE CLASSIFICATION 101 DST PRUNE 102 JOIN FILTER 103 HASH 104 ERROR LOGGING 105 LOAD TABLE CONVENTIONAL 106 PART JOIN FILTER 107 AW SCAN 108 PARTITION REFERENCE 109 PX PARTITION REFERENCE 110 XPATH EVALUATION 111 RESULT CACHE 112 DM FEED PREDICTOR-TARGET PAIRS 113 DM SUPERVISED BINNING 114 DM HASH-BROADCAST COUNTS 115 BULK BINDS GET 116 APPROXIMATE NDV 117 UNPIVOT 118 TRANSPOSE 119 MONITORING 120 CUBE SCAN 121 JOINED CUBE SCAN 122 CUBE ACCESS 123 MAT_VIEW CUBE ACCESS 124 MAT_VIEW REWRITE CUBE ACCESS 125 rows selected.
options can be listed by querying X$XPLTOO (Explain Plan Table Operation Options).
SQL> SELECT xpltoo_id operation_options_id, 2 xpltoo_name operation_options_name 3 FROM x$xpltoo; OPERATION_OPTIONS_ID OPERATION_OPTIONS_NAME -------------------- ---------------------------------------- 0 1 OUTER 2 ANTI 3 SEMI 4 CARTESIAN 5 TO ROWIDS 6 FROM ROWIDS 7 COUNT 8 SINGLE VALUE 9 SAMPLE FAST FULL SCAN 10 FAST FULL SCAN 11 FULL SCAN 12 RANGE SCAN 13 UNIQUE SCAN 14 SKIP SCAN 15 FULL SCAN (MIN/MAX) 16 RANGE SCAN (MIN/MAX) 17 FULL SCAN DESCENDING 18 RANGE SCAN DESCENDING 19 SKIP SCAN DESCENDING 20 WITH FILTERING 21 WITHOUT FILTERING 22 STOPKEY 23 FIXED INDEX 24 FULL 25 HASH 26 INDEX BUILD 27 UNIQUE (LOCAL) 28 NON UNIQUE (LOCAL) 29 UNIQUE 30 NON UNIQUE 31 PICKLER FETCH 32 CONSTRUCTOR FETCH 33 SUBQUERY FETCH 34 REMOTE 35 SAMPLE 36 SAMPLE BY ROWID RANGE 37 BY ROWID RANGE 38 BY LOCAL INDEX ROWID 39 BY GLOBAL INDEX ROWID 40 BY INDEX ROWID 41 BY USER ROWID 42 CLUSTER 43 NOSORT 44 BUFFER PUSHED RANK 45 BUFFER 46 CHILD PUSHED RANK 47 CHILD 48 SORT PUSHED RANK 49 SINGLE 50 EMPTY 51 INLIST 52 ALL 53 ITERATOR 54 PARTITION 55 AGGREGATE 56 GROUP BY STOPKEY 57 GROUP BY 58 UNIQUE NOSORT 59 GROUP BY NOSORT 60 GROUP BY ROLLUP 61 CUBE 62 ROLLUP 63 SORT 64 CREATE INDEX 65 UNIQUE STOPKEY 66 ORDER BY STOPKEY 67 ORDER BY 68 JOIN 69 GROUP BY NOSORT ROLLUP 70 CUSTOM OPTION 71 ROWID RANGE 72 EXTERNAL CHUNK 73 ACYCLIC 74 CYCLIC 75 ACYCLIC FAST 76 ORDERED 77 ORDERED FAST 78 RIGHT OUTER 79 RIGHT ANTI 80 RIGHT SEMI 81 PARTITION OUTER 82 (LOCAL) 83 PARTITION (ROWID) 84 PARTITION (KEY) 85 RANGE 86 ROUND-ROBIN 87 BROADCAST 88 QC (ORDER) 89 QC (RANDOM) 90 BROADCAST LOCAL 91 HASH LOCAL 92 HYBRID (ROWID PKEY) 93 RANDOM LOCAL 94 HASH (BLOCK ADDRESS) 95 MIXED HASH BCAST LOCAL 96 MIXED HASH BCAST 97 OUTER BUFFERED 98 RIGHT OUTER BUFFERED 99 ANTI BUFFERED 100 RIGHT ANTI BUFFERED 101 SEMI BUFFERED 102 RIGHT SEMI BUFFERED 103 CARTESIAN BUFFERED 104 PARTITION OUTER BUFFERED 105 HASH ALL 106 RANGE ALL 107 LIST 108 LIST ALL 109 SYSTEM 110 FORCED SERIAL 111 PARTITION JOIN 112 AW HASH 113 CREATE 114 USE 115 SUBQUERY 116 OR 117 MULTI-COLUMN 118 NOSORT STOPKEY 119 BUFFERED 120 FULL OUTER 121 FULL OUTER BUFFERED 122 NO FILTERING WITH START-WITH 123 JOIN-FILTER 124 ANTI NA 125 ANTI SNA 126 ANTI NA BUFFERED 127 ANTI SNA BUFFERED 128 RIGHT ANTI NA 129 RIGHT ANTI SNA 130 RIGHT ANTI NA BUFFERED 131 RIGHT ANTI SNA BUFFERED 132 GROUP BY PIVOT 133 GROUP BY NOSORT PIVOT 134 DUMP 135 VERIFY 136 STRIP 137 STORAGE FULL 138 STORAGE SAMPLE 139 STORAGE SAMPLE BY ROWID RANGE 140 STORAGE BY ROWID RANGE 141 PARTIAL OUTER 142 OPTIMIZED 143 SPECIFIED 144 STORAGE SAMPLE FAST FULL SCAN 145 STORAGE FAST FULL SCAN 146 STORAGE FULL SCAN 147 STORAGE RANGE SCAN 148 STORAGE FULL SCAN (MIN/MAX) 149 rows selected.
Also, in 11g R2, the same information is available via Automatic Workload Repository (AWR) views, DBA_HIST_PLAN_OPERATION_NAME and DBA_HIST_PLAN_OPTION_NAME.
SQL> SELECT banner FROM v$version WHERE ROWNUM = 1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production SQL> SELECT 2 operation_id, 3 operation_name 4 FROM 5 DBA_HIST_PLAN_OPERATION_NAME; OPERATION_ID OPERATION_NAME ------------ ---------------------------------------------------------------- 0 1 BITMAP KEY ITERATION 2 NESTED LOOPS 3 MERGE JOIN 4 HASH JOIN 5 AND-EQUAL 6 BITMAP AND 7 INTERSECTION 8 MINUS 9 BITMAP MINUS 10 GENERATE 11 BITMAP CONVERSION 12 BITMAP INDEX 13 CONNECT BY 14 CONNECT BY PUMP 15 COUNT 16 DELETE 17 UPDATE 18 FIRST ROW 19 FILTER 20 FOR UPDATE 21 FIXED TABLE 22 INLIST ITERATOR 23 INDEX 24 INDEX BUILD 25 LOAD AS SELECT 26 MULTI-TABLE INSERT 27 INTO 28 DIRECT LOAD INTO 29 COLLECTION ITERATOR 30 DOMAIN INDEX 31 PARTITION RANGE 32 PARTITION HASH 33 PARTITION LIST 34 PARTITION SYSTEM 35 PARTITION COMBINED 36 GRANULE ITERATOR 37 SEQUENCE 38 TABLE ACCESS 39 TABLE QUEUE 40 INDEX MAINTENANCE 41 FIFO BUFFER 42 WINDOW 43 TEMP TABLE TRANSFORMATION 44 SORT 45 BUFFER 46 BITMAP COMPACTION 47 BITMAP CONSTRUCTION 48 BITMAP OR 49 BITMAP MERGE 50 CONCATENATION 51 UNION-ALL 52 VIEW PUSHED PREDICATE 53 VIEW 54 REMOTE 55 SELECT STATEMENT 56 INSERT STATEMENT 57 UPDATE STATEMENT 58 MERGE STATEMENT 59 DELETE STATEMENT 60 CREATE TABLE STATEMENT 61 ALTER INDEX STATEMENT 62 CREATE INDEX STATEMENT 63 DDL STATEMENT 64 Can't Explain!! 65 TEMP TABLE GENERATION 66 TRUNCATE TEMP TABLE 67 RECURSIVE EXECUTION 68 MERGE 69 EXTERNAL TABLE ACCESS 70 BITMAP JOIN INDEX UPDATE 71 BITMAP JOIN INDEX UPDATE STATEMENT 72 VIEW HIERARCHY 73 SQL MODEL 74 MAT_VIEW ACCESS 75 MAT_VIEW REWRITE ACCESS 76 FREQUENT ITEMSET COUNTING 77 FIC ENUMERATE FEED 78 FIC LOAD BITMAPS 79 FIC LOAD ITEMSETS 80 FIC DETECT END 81 FIC RECURSIVE ITERATION 82 DOMAIN INDEX BUILD 83 DOMAIN INDEX PARTITION BUILD 84 FAST DUAL 85 REFERENCE MODEL 86 PX RECEIVE 87 QC SEND 88 PX SEND 89 PX COORDINATOR 90 PX BLOCK 91 PX PARTITION RANGE 92 PX PARTITION HASH 93 PX PARTITION LIST 94 WINDOW (IN SQL MODEL) 95 UNION ALL PUSHED PREDICATE 96 DST ENUMERATE FEED 97 DST DETECT END 98 DST RECURSIVE ITERATION 99 DST SPLIT 100 DECISION TREE CLASSIFICATION 101 DST PRUNE 102 JOIN FILTER 103 HASH 104 ERROR LOGGING 105 LOAD TABLE CONVENTIONAL 106 PART JOIN FILTER 107 AW SCAN 108 PARTITION REFERENCE 109 PX PARTITION REFERENCE 110 XPATH EVALUATION 111 RESULT CACHE 112 DM FEED PREDICTOR-TARGET PAIRS 113 DM SUPERVISED BINNING 114 DM HASH-BROADCAST COUNTS 115 BULK BINDS GET 116 APPROXIMATE NDV 117 UNPIVOT 118 TRANSPOSE 119 MONITORING 120 CUBE SCAN 121 JOINED CUBE SCAN 122 CUBE ACCESS 123 MAT_VIEW CUBE ACCESS 124 MAT_VIEW REWRITE CUBE ACCESS 125 CUBE TABLE SCAN 126 JOINED CUBE TABLE SCAN 127 CUBE TABLE ACCESS 128 UNION ALL (RECURSIVE WITH) 129 RECURSIVE WITH PUMP 130 rows selected. SQL> SELECT 2 option_id, 3 option_name 4 FROM 5 DBA_HIST_PLAN_OPTION_NAME; OPTION_ID OPTION_NAME ---------- ---------------------------------------------------------------- 0 1 OUTER 2 ANTI 3 SEMI 4 CARTESIAN 5 TO ROWIDS 6 FROM ROWIDS 7 COUNT 8 SINGLE VALUE 9 SAMPLE FAST FULL SCAN 10 FAST FULL SCAN 11 FULL SCAN 12 RANGE SCAN 13 UNIQUE SCAN 14 SKIP SCAN 15 FULL SCAN (MIN/MAX) 16 RANGE SCAN (MIN/MAX) 17 FULL SCAN DESCENDING 18 RANGE SCAN DESCENDING 19 SKIP SCAN DESCENDING 20 WITH FILTERING 21 WITHOUT FILTERING 22 STOPKEY 23 FIXED INDEX 24 FULL 25 HASH 26 INDEX BUILD 27 UNIQUE (LOCAL) 28 NON UNIQUE (LOCAL) 29 UNIQUE 30 NON UNIQUE 31 PICKLER FETCH 32 CONSTRUCTOR FETCH 33 SUBQUERY FETCH 34 REMOTE 35 SAMPLE 36 SAMPLE BY ROWID RANGE 37 BY ROWID RANGE 38 BY LOCAL INDEX ROWID 39 BY GLOBAL INDEX ROWID 40 BY INDEX ROWID 41 BY USER ROWID 42 CLUSTER 43 NOSORT 44 BUFFER PUSHED RANK 45 BUFFER 46 CHILD PUSHED RANK 47 CHILD 48 SORT PUSHED RANK 49 SINGLE 50 EMPTY 51 INLIST 52 ALL 53 ITERATOR 54 PARTITION 55 AGGREGATE 56 GROUP BY STOPKEY 57 GROUP BY 58 UNIQUE NOSORT 59 GROUP BY NOSORT 60 GROUP BY ROLLUP 61 CUBE 62 ROLLUP 63 SORT 64 CREATE INDEX 65 UNIQUE STOPKEY 66 ORDER BY STOPKEY 67 ORDER BY 68 JOIN 69 GROUP BY NOSORT ROLLUP 70 CUSTOM OPTION 71 ROWID RANGE 72 EXTERNAL CHUNK 73 ACYCLIC 74 CYCLIC 75 ACYCLIC FAST 76 ORDERED 77 ORDERED FAST 78 RIGHT OUTER 79 RIGHT ANTI 80 RIGHT SEMI 81 PARTITION OUTER 82 (LOCAL) 83 PARTITION (ROWID) 84 PARTITION (KEY) 85 RANGE 86 ROUND-ROBIN 87 BROADCAST 88 QC (ORDER) 89 QC (RANDOM) 90 BROADCAST LOCAL 91 HASH LOCAL 92 HYBRID (ROWID PKEY) 93 RANDOM LOCAL 94 HASH (BLOCK ADDRESS) 95 MIXED HASH BCAST LOCAL 96 MIXED HASH BCAST 97 OUTER BUFFERED 98 RIGHT OUTER BUFFERED 99 ANTI BUFFERED 100 RIGHT ANTI BUFFERED 101 SEMI BUFFERED 102 RIGHT SEMI BUFFERED 103 CARTESIAN BUFFERED 104 PARTITION OUTER BUFFERED 105 HASH ALL 106 RANGE ALL 107 LIST 108 LIST ALL 109 SYSTEM 110 FORCED SERIAL 111 PARTITION JOIN 112 AW HASH 113 CREATE 114 USE 115 SUBQUERY 116 OR 117 MULTI-COLUMN 118 NOSORT STOPKEY 119 BUFFERED 120 FULL OUTER 121 FULL OUTER BUFFERED 122 NO FILTERING WITH START-WITH 123 JOIN-FILTER 124 ANTI NA 125 ANTI SNA 126 ANTI NA BUFFERED 127 ANTI SNA BUFFERED 128 RIGHT ANTI NA 129 RIGHT ANTI SNA 130 RIGHT ANTI NA BUFFERED 131 RIGHT ANTI SNA BUFFERED 132 GROUP BY PIVOT 133 GROUP BY NOSORT PIVOT 134 DUMP 135 VERIFY 136 STRIP 137 STORAGE FULL 138 STORAGE SAMPLE 139 STORAGE SAMPLE BY ROWID RANGE 140 STORAGE BY ROWID RANGE 141 PARTIAL OUTER 142 OPTIMIZED 143 SPECIFIED 144 STORAGE SAMPLE FAST FULL SCAN 145 STORAGE FAST FULL SCAN 146 STORAGE FULL SCAN 147 STORAGE RANGE SCAN 148 STORAGE FULL SCAN (MIN/MAX) 149 WITH FILTERING (UNIQUE) 150 WITHOUT FILTERING (UNIQUE) 151 NO FILTERING WITH SW (UNIQUE) 152 BREADTH FIRST 153 DEPTH FIRST 154 AND 155 rows selected.
If you look the output closely, you will notice that Oracle 11g R2 has below additional operations and options listed:
- 11g R2 Operations (line 138-144 highlighted above):
CUBE TABLE SCAN JOINED CUBE TABLE SCAN CUBE TABLE ACCESS UNION ALL (RECURSIVE WITH) RECURSIVE WITH PUMP
- 11g R2 Options (line 305-310 highlighted above):
WITH FILTERING (UNIQUE) WITHOUT FILTERING (UNIQUE) NO FILTERING WITH SW (UNIQUE) BREADTH FIRST DEPTH FIRST AND