Oracle Execution Plan Row Source Operator Reference


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:

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

Oracle Rowsource Operator Options list

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