7 PLAN_TABLE Reference
This chapter describes PLAN_TABLE
columns.
7.1 PLAN_TABLE Columns
PLAN_TABLE
is populated by the EXPLAIN PLAN
statement.
The following table describes the columns in PLAN_TABLE
.
Table 7-1 PLAN_TABLE Columns
Column | Type | Description |
---|---|---|
|
|
Value of the optional |
|
|
Unique identifier of a plan in the database. |
|
|
Date and time when the |
|
|
Any comment (of up to 80 bytes) you want to associate with each step of the explained plan. This column indicates whether the database used an outline or SQL profile for the query. If you need to add or change a remark on any row of the |
|
|
Name of the internal operation performed in this step. In the first row generated for a statement, the column contains one of the following values:
See "OPERATION and OPTION Columns of PLAN_TABLE" for more information about values for this column. |
|
|
A variation on the operation that the See "OPERATION and OPTION Columns of PLAN_TABLE" for more information about values for this column. |
|
|
Name of the database link used to reference the object (a table name or view name). For local queries using parallel execution, this column describes the order in which the database consumes output from operations. |
|
|
Name of the user who owns the schema containing the table or index. |
|
|
Name of the table or index. |
|
|
Unique alias of a table or view in a SQL statement. For indexes, it is the object alias of the underlying table. |
|
|
Number corresponding to the ordinal position of the object as it appears in the original statement. The numbering proceeds from left to right, outer to inner for the original statement text. View expansion results in unpredictable numbers. |
|
|
Modifier that provides descriptive information about the object; for example, |
|
|
Current mode of the optimizer. |
|
|
Not currently used. |
|
|
A number assigned to each step in the execution plan. |
|
|
The ID of the next execution step that operates on the output of the |
|
|
Depth of the operation in the row source tree that the plan represents. You can use this value to indent the rows in a plan table report. |
|
|
For the first row of output, this indicates the estimated cost of executing the statement. For the other rows, it indicates the position relative to the other children of the same parent. |
|
|
Cost of the operation as estimated by the optimizer. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is a weighted value used to compare costs of execution plans. The value of this column is a function of the |
|
|
Estimate by the query optimization approach of the number of rows that the operation accessed. |
|
|
Estimate by the query optimization approach of the number of bytes that the operation accessed. |
|
|
Describes the contents of the
|
|
|
Start partition of a range of accessed partitions. It can take one of the following values: n indicates that the start partition has been identified by the SQL compiler, and its partition number is given by n.
|
|
|
Stop partition of a range of accessed partitions. It can take one of the following values: n indicates that the stop partition has been identified by the SQL compiler, and its partition number is given by n.
|
|
|
Step that has computed the pair of values of the |
|
|
Other information that is specific to the execution step that a user might find useful. See the |
|
|
Method used to distribute rows from producer query servers to consumer query servers. See "DISTRIBUTION Column of PLAN_TABLE" for more information about the possible values for this column. For more information about consumer and producer query servers, see Oracle Database VLDB and Partitioning Guide. |
|
|
CPU cost of the operation as estimated by the optimizer. The value of this column is proportional to the number of machine cycles required for the operation. For statements that use the rule-based approach, this column is null. |
|
|
I/O cost of the operation as estimated by the optimizer. The value of this column is proportional to the number of data blocks read by the operation. For statements that use the rule-based approach, this column is null. |
|
|
Temporary space, in bytes, used by the operation as estimated by the optimizer. For statements that use the rule-based approach, or for operations that do not use any temporary space, this column is null. |
|
|
Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan. |
|
|
Predicates used to filter rows before producing them. |
|
|
Expressions produced by the operation. |
|
|
Elapsed time in seconds of the operation as estimated by query optimization. For statements that use the rule-based approach, this column is null. |
|
|
Name of the query block, either system-generated or defined by the user with the |
"OPERATION and OPTION Columns of PLAN_TABLE" lists each combination of OPERATION
and OPTIONS
produced by the EXPLAIN PLAN
statement and its meaning within an execution plan.
See Also:
Oracle Database
Reference for more information about PLAN_TABLE
7.2 OPERATION and OPTION Columns of PLAN_TABLE
This table lists each combination of the OPERATION
and OPTIONS
columns of PLAN_TABLE
and their meaning within an execution plan.
Table 7-2 OPERATION and OPTIONS Values Produced by EXPLAIN PLAN
Operation | Option | Description |
---|---|---|
|
|
Operation accepting multiple sets of rowids, returning the intersection of the sets, eliminating duplicates. Used for the single-column indexes access path. |
|
|
|
|
|
|
|
|
Merges several bitmaps resulting from a range scan into one bitmap. |
|
|
Subtracts bits of one bitmap from another. Row source is used for negated predicates. This option is usable only if there are non-negated predicates yielding a bitmap from which the subtraction can take place. |
|
|
Computes the bitwise |
|
|
Computes the bitwise |
|
|
Takes each row from a table row source and finds the corresponding bitmap from a bitmap index. This set of bitmaps are then merged into one bitmap in a following |
|
|
Retrieves rows in hierarchical order for a query containing a |
|
|
Operation accepting multiple sets of rows returning the union-all of the sets. |
|
|
Operation counting the number of rows selected from a table. |
|
|
Count operation where the number of rows returned is limited by the |
|
|
Joins a table or view on the left and a cube on the right. See Oracle Database SQL
Language Reference to learn about the |
|
|
Uses an antijoin for a table or view on the left and a cube on the right. |
|
|
Uses an antijoin (single-sided null aware) for a table or view on the left and a cube on the right. The join column on the right (cube side) is |
|
|
Uses an outer join for a table or view on the left and a cube on the right. |
|
|
Uses a right semijoin for a table or view on the left and a cube on the right. |
|
|
Uses inner joins for all cube access. |
|
|
Uses an outer join for at least one dimension, and inner joins for the other dimensions. |
|
|
Uses outer joins for all cube access. |
|
|
Retrieval of one or more rowids from a domain index. The options column contain information supplied by a user-defined domain index cost function, if any. |
|
|
Operation accepting a set of rows, eliminates some of them, and returns the rest. |
|
|
Retrieval of only the first row selected by a query. |
|
|
Operation retrieving and locking the rows selected by a query containing a |
|
|
Operation hashing a set of rows into groups for a query with a |
|
|
Operation hashing a set of rows into groups for a query with a |
(These are join operations.) |
|
Operation joining two sets of rows and returning the result. This join method is useful for joining large data sets of data (DSS, Batch). The join condition is an efficient way of accessing the second table. Query optimizer uses the smaller of the two tables/data sources to build a hash table on the join key in memory. Then it scans the larger table, probing the hash table to find the joined rows. |
|
|
Hash (left) antijoin |
|
|
Hash (left) semijoin |
|
|
Hash right antijoin |
|
|
Hash right semijoin |
|
|
Hash (left) outer join |
|
|
Hash right outer join |
(These are access methods.) |
|
Retrieval of a single rowid from an index. |
|
|
Retrieval of one or more rowids from an index. Indexed values are scanned in ascending order. |
|
|
Retrieval of one or more rowids from an index. Indexed values are scanned in descending order. |
|
|
Retrieval of all rowids from an index when there is no start or stop key. Indexed values are scanned in ascending order. |
|
|
Retrieval of all rowids from an index when there is no start or stop key. Indexed values are scanned in descending order. |
|
|
Retrieval of all rowids (and column values) using multiblock reads. No sorting order can be defined. Compares to a full table scan on only the indexed columns. Only available with the cost based optimizer. |
|
|
Retrieval of rowids from a concatenated index without using the leading column(s) in the index. Only available with the cost based optimizer. |
|
|
Iterates over the next operation in the plan for each value in the |
|
|
Operation accepting two sets of rows and returning the intersection of the sets, eliminating duplicates. |
(These are join operations.) |
|
Operation accepting two sets of rows, each sorted by a value, combining each row from one set with the matching rows from the other, and returning the result. |
|
|
Merge join operation to perform an outer join statement. |
|
|
Merge antijoin. |
|
|
Merge semijoin. |
|
|
Can result from 1 or more of the tables not having any join conditions to any other tables in the statement. Can occur even with a join and it may not be flagged as |
|
|
Retrieval of rows in hierarchical order for a query containing a |
(These are access methods.) |
|
Retrieval of all rows from a materialized view. |
|
|
Retrieval of sampled rows from a materialized view. |
|
|
Retrieval of rows from a materialized view based on a value of an indexed cluster key. |
|
|
Retrieval of rows from materialized view based on hash cluster key value. |
|
|
Retrieval of rows from a materialized view based on a rowid range. |
|
|
Retrieval of sampled rows from a materialized view based on a rowid range. |
|
|
If the materialized view rows are located using user-supplied rowids. |
|
|
If the materialized view is nonpartitioned and rows are located using indexes. |
|
|
If the materialized view is partitioned and rows are located using only global indexes. |
|
|
If the materialized view is partitioned and rows are located using one or more local indexes and possibly some global indexes. Partition Boundaries: The partition boundaries might have been computed by: A previous The |
|
|
Operation accepting two sets of rows and returning rows appearing in the first set but not in the second, eliminating duplicates. |
(These are join operations.) |
|
Operation accepting two sets of rows, an outer set and an inner set. Oracle Database compares each row of the outer set with each row of the inner set, returning rows that satisfy a condition. This join method is useful for joining small subsets of data (OLTP). The join condition is an efficient way of accessing the second table. |
|
|
Nested loops operation to perform an outer join statement. |
|
|
Iterates over the next operation in the plan for each partition in the range given by the |
|
|
Access one partition. |
|
|
Access many partitions (a subset). |
|
|
Access all partitions. |
|
|
Similar to iterator, but based on an |
|
|
Indicates that the partition set to be accessed is empty. |
POLYMORPHIC TABLE FUNCTION |
|
Indicates the row source for a polymorphic table function, which is a table function whose return type is determined by its arguments. |
|
|
Implements the division of an object into block or chunk ranges among a set of parallel execution servers. |
|
|
Implements the Query Coordinator which controls, schedules, and executes the parallel plan below it using parallel execution servers. It also represents a serialization point, as the end of the part of the plan executed in parallel and always has a |
|
|
Same semantics as the regular |
|
|
Shows the consumer/receiver parallel execution node reading repartitioned data from a send/producer (QC or parallel execution server) executing on a PX SEND node. This information was formerly displayed into the |
|
|
Implements the distribution method taking place between two sets of parallel execution servers. Shows the boundary between two sets and how data is repartitioned on the send/producer side (QC or side. This information was formerly displayed into the |
|
|
Retrieval of data from a remote database. |
|
|
Operation involving accessing values of a sequence. |
|
|
Retrieval of a single row after applying a group function to a set of selected rows. In this case, the database “sorts” a single row. |
|
|
Operation sorting a set of rows to eliminate duplicates. |
|
|
Operation sorting a set of rows into groups for a query with a |
|
|
Operation sorting a set of rows into groups for a query with a |
|
|
Operation sorting a set of rows before a merge-join. |
|
|
Operation sorting a set of rows for a query with an |
(These are access methods.) |
|
Retrieval of all rows from a table.
|
|
|
Retrieval of sampled rows from a table. |
|
|
Retrieval of rows from a table based on a value of an indexed cluster key. |
|
|
Retrieval of rows from table based on hash cluster key value. |
|
|
Retrieval of rows from a table based on a rowid range. |
|
|
Retrieval of sampled rows from a table based on a rowid range. |
|
|
If the table rows are located using user-supplied rowids. |
|
|
If the table is nonpartitioned and rows are located using index(es). |
|
|
If the table is partitioned and rows are located using only global indexes. |
|
|
If the table is partitioned and rows are located using one or more local indexes and possibly some global indexes. Partition Boundaries: The partition boundaries might have been computed by: A previous The |
|
|
Operation evaluating a |
|
|
Operation accepting two sets of rows and returns the union of the sets, eliminating duplicates. |
|
|
Operation that rotates data from columns into rows. |
|
|
Operation performing a view's query and then returning the resulting rows to another operation. |
7.3 DISTRIBUTION Column of PLAN_TABLE
The DISTRIBUTION
column indicates the method used to distribute rows from producer query servers to consumer query servers.
Table 7-3 Values of DISTRIBUTION Column of the PLAN_TABLE
DISTRIBUTION Text | Description |
---|---|
|
Maps rows to query servers based on the partitioning of a table or index using the rowid of the row to |
|
Maps rows to query servers based on the partitioning of a table or index using a set of columns. Used for partial partition-wise join, |
|
Maps rows to query servers using a hash function on the join key. Used for |
|
Maps rows to query servers using ranges of the sort key. Used when the statement contains an |
|
Randomly maps rows to query servers. |
|
Broadcasts the rows of the entire table to each query server. Used for a parallel join when one table is very small compared to the other. |
|
The QC consumes the input in order, from the first to the last query server. Used when the statement contains an |
|
The QC consumes the input randomly. Used when the statement does not have an |