Describing the PLAN Table Columns

The SYS.PLAN table has seven columns.

  • Column 1 (Step)

    Indicates the order of operation, which always starts with one. This example uses a table lock range scan in the following order:

    1. Table locking range scan of IX1 on table t1.

    2. Table locking range scan of IX2 on t2.

    3. Merge join of t1 and t2 and so forth.

  • Column 2 (Level)

    Indicates the position of the operation in the join-tree diagram that describes the query processing. For this example, the join tree is as follows:

    Description of jointree.eps follows
    Description of the illustration jointree.eps

  • Column 3 (Operation)

    Indicates the type of operation being run. For a description of the potential values in this field and the type of table scan each represents, see SYS.PLAN in System Tables and Views in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

    Not all operations the optimizer performs are visible to the user. Only operations significant to performance analysis are shown in the SYS.PLAN table. TblLk is an optimizer hint that is honored at runtime in Serializable or Read Committed isolation. Table locks are used during a scan only if row locks are disabled during preparation.

  • Column 4 (TblNames)

    Indicates the table that is being scanned. This column is used only when the operation is a scan. In all other cases, this column is NULL.

  • Column 5 (IXName)

    Indicates the index that is being used. This column is used only when the operation is an index scan using an existing index—such as a hash or range scan. In all other cases, this column is NULL. Names of range indexes are followed with "(D)" if the scan is descending—from large to small rather than from small to large.

  • Column 6 (Pred)

    Indicates the predicate that participates in the operation, if there is one. Predicates are used only with index scan and MergeJoin operations. The predicate character string is limited to 1,024 characters.

    This column may be NULL—indicating no predicate—for a range scan. The optimizer may choose a range scan over a table scan because, in addition to filtering, it has two useful properties:

    • Rows are returned in sorted order, on index key.

    • Rows may be returned faster, especially if the table is sparse.

    In this example, the range scans are used for their sorting capability; none of them evaluates a predicate.

  • Column 7 (Other Pred)

    Indicates any other predicate that is applied while the operation runs. These predicates do not participate directly in the scan or join but are evaluated on each row returned by the scan or join.

    For example, at step two of the plan generated for this example, a range scan is performed on table t2. When that scan is performed, the predicate t2.b <> 0 is also evaluated. Similarly, once the final merge-join has been performed, it is then possible to evaluate the predicate t3.b / t1.b > 1.