Oracle Execution Plan Row Source Operator Reference

Overview

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).

Note that a few rowsources in Oracle are "hidden", these are not listed here (nor in explain plan / V$SQL_PLAN output).

An example of a hidden rowsource is the Statistics rowsource, which measures time and passes rows through from its child operator. The usage of hidden rowsources can be detected using OS level tools like Tanel's os_explain command. Another hidden rowsource is a plain filter rowsource, which throws away rows not matching the filter condition. This filter rowsource does not show up as FILTER operation (which is a different thing), but is rather just shown as "filter -" in the predicate section of execution plan output.

Different operations do different things and they only know how to do "their thing". For example, a TABLE ACCESS operation does not know how walk an index and find ROWID's from there, instead the TABLE ACCESS will call INDEX rowsource which walks the index structure and returns a list of ROWID's (physical row addresses) to the TABLE ACCESS. Then the TABLE ACCESS uses these ROWIDs to fetch their corresponding rows from their physical locations.

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

• 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. TEMP TABLE GENERATION

  65. TRUNCATE TEMP TABLE

  66. RECURSIVE EXECUTION

  67. MERGE

  68. EXTERNAL TABLE ACCESS

  69. BITMAP JOIN INDEX UPDATE

  70. BITMAP JOIN INDEX UPDATE STATEMENT

  71. VIEW HIERARCHY

  72. SQL MODEL

  73. MAT_VIEW ACCESS

  74. MAT_VIEW REWRITE ACCESS

  75. FREQUENT ITEMSET COUNTING

  76. FIC ENUMERATE FEED

  77. FIC LOAD BITMAPS

  78. FIC LOAD ITEMSETS

  79. FIC DETECT END

  80. FIC RECURSIVE ITERATION

  81. DOMAIN INDEX BUILD

  82. DOMAIN INDEX PARTITION BUILD

  83. FAST DUAL

  84. REFERENCE MODEL

  85. PX RECEIVE

  86. QC SEND

  87. PX SEND

  88. PX COORDINATOR

  89. PX BLOCK

  90. PX PARTITION RANGE

  91. PX PARTITION HASH

  92. PX PARTITION LIST

  93. WINDOW (IN SQL MODEL)

  94. UNION ALL PUSHED PREDICATE

  95. DST ENUMERATE FEED

  96. DST DETECT END

  97. DST RECURSIVE ITERATION

  98. DST SPLIT

  99. DECISION TREE CLASSIFICATION

  100. DST PRUNE

  101. JOIN FILTER

  102. HASH

  103. ERROR LOGGING

  104. LOAD TABLE CONVENTIONAL

  105. PART JOIN FILTER

  106. AW SCAN

  107. PARTITION REFERENCE

  108. PX PARTITION REFERENCE

  109. XPATH EVALUATION

  110. RESULT CACHE

  111. DM FEED PREDICTOR-TARGET PAIRS

  112. DM SUPERVISED BINNING

  113. DM HASH-BROADCAST COUNTS

  114. BULK BINDS GET

  115. APPROXIMATE NDV

  116. UNPIVOT

  117. TRANSPOSE

  118. MONITORING

  119. CUBE SCAN

  120. JOINED CUBE SCAN

  121. CUBE ACCESS

  122. MAT_VIEW CUBE ACCESS

  123. MAT_VIEW REWRITE CUBE ACCESS

  124. CUBE TABLE SCAN

  125. JOINED CUBE TABLE SCAN

  126. CUBE TABLE ACCESS

  127. UNION ALL (RECURSIVE WITH)

  128. RECURSIVE WITH PUMP

Oracle Rowsource Operator Options list

• 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

I will (slowly) start adding additional documentation about these operators and options here... Feel free to request which ones I should document first at the Oracle Living Book entry page