Copyright

Oracle Living Books‎ > ‎SQL‎ > ‎

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

Comments