Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - SQL Usage » Query Processing » Query optimization and execution » Reading execution plans

 

Execution plan abbreviations

Following are the abbreviations that you see in execution plans.

Short text plan Long text plan Additional information
Costed Best Plan

The optimizer generates and costs access plans for a given query. During this process the current best plan maybe replaced by a new best plan found to have a lower cost estimate. The last best plan is the execution plan used to execute the statement. Costed Best Plans indicates the number of times the optimizer found a better plan than the current best plan. A low number indicates that the best plan was determined early in the enumeration process. Since the optimizer starts the enumeration process at least once for each query block in the given statement, Costed Best Plans represents the cumulative count. See How the optimizer works.

Costed Plans

Many plans generated by the optimizer are found to be too expensive compared to the best plan found so far. Costed Plans represents the number of partial or complete plans the optimizer considered during the enumeration processes for a given statement.

DELETE Delete The root node of a DELETE operation. See DELETE statement.
DistH HashDistinct

See HashDistinct algorithm (DistH).

DistO OrderedDistinct

See OrderedDistinct algorithm (DistO).

DP DecodePostings

See DecodePostings (DP).

DT DerivedTable

See DerivedTable algorithm (DT).

EAH HashExceptAll

See Except algorithms (EAH, EAM, EH, EM).

EAM MergeExceptAll

See Except algorithms (EAH, EAM, EH, EM).

EH HashExcept

See Except algorithms (EAH, EAM, EH, EM).

EM MergeAccept

See Except algorithms (EAH, EAM, EH, EM).

Exchange Exchange

See Exchange algorithm (Exchange).

Filter Filter

See Filter algorithms (Filter, PreFilter).

GrByH HashGroupBy

See HashGroupBy algorithm (GrByH).

GrByHClust HashGroupByClustered

See ClusteredHashGroupBy algorithm (GrByHClust).

GrByHSets HashGroupBySets

See HashGroupBySets algorithm (GrByHSets).

GrByO OrderedGroupBy

See OrderedGroupBy algorithm (GrByO).

GrByOSets OrderedGroupBySets

See OrderedGroupBySets algorithm (GrByOSets).

GrByS SingleRowGroupBy

See SingleRowGroupBy algorithm (GrByS).

GrBySSets SortedGroupBySets

See SortedGroupBySets algorithm (GrBySSets).

HF HashFilter

See Hash filter algorithms (HF, HFP).

HFP ParallelHashFilter

See Hash filter algorithms (HF, HFP).

HTS HashTableScan

See HashTableScan method (HTS).

IAH HashIntersectAll

See Intersect algorithms (IH, IM, IAH, IAM).

IAM MergeIntersectAll

See Intersect algorithms (IH, IM, IAH, IAM).

IH HashIntersect

See Intersect algorithms (IH, IM, IAH, IAM).

IM MergeIntersect

See Intersect algorithms (IH, IM, IAH, IAM).

IN InList

See InList algorithm (IN).

table-name<index-name>

IndexScan, ParallelIndexScan

In a graphical plan, an index scan appears as an index name in a trapezoid. See IndexScan method.

INSENSITIVE Insensitive

See Intersect algorithms (IH, IM, IAH, IAM).

INSERT Insert

Root node of an insert operation. See INSERT statement.

IO IndexOnlyScan, ParallelIndexOnlyScan

See IndexOnlyScan method (IO), and ParallelIndexScan method.

JH HashJoin

See HashJoin algorithms (JH, JHSP, JHFO, JHAP, JHO, JHPO).

JHS HashSemijoin

See HashSemijoin algorithm (JHS).

JHSP ParallelHashSemijoin

See HashJoin algorithms (JH, JHSP, JHFO, JHAP, JHO, JHPO).

JHFO Full Outer HashJoin

See HashJoin algorithms (JH, JHSP, JHFO, JHAP, JHO, JHPO).

JHA HashAntisemijoin

See HashAntisemijoin algorithm (JHA).

JHAP ParallelHashAntisemijoin

See HashJoin algorithms (JH, JHSP, JHFO, JHAP, JHO, JHPO).

JHO Left Outer HashJoin

See HashJoin algorithms (JH, JHSP, JHFO, JHAP, JHO, JHPO).

JHP ParallelHashJoin

See HashJoin algorithms (JH, JHSP, JHFO, JHAP, JHO, JHPO).

JHPO ParallelLeftOuterHashJoin

See HashJoin algorithms (JH, JHSP, JHFO, JHAP, JHO, JHPO).

JHR RecursiveHashJoin

See RecursiveHashJoin algorithm (JHR).

JHRO RecursiveLeftOuterHashJoin

See RecursiveLeftOuterHashJoin algorithm (JHRO).

JM MergeJoin

See MergeJoin algorithms (JM, JMFO, JMO).

JMFO Full Outer MergeJoin

See MergeJoin algorithms (JM, JMFO, JMO).

JMO Left Outer MergeJoin

See MergeJoin algorithms (JM, JMFO, JMO).

JNL NestedLoopsJoin

See NestedLoopsJoin algorithms (JNL, JNLFO, JNLO).

JNLA NestedLoopsAntisemijoin

See NestedLoopsAntisemijoin algorithm (JNLA).

JNLFO Full Outer NestedLoopsJoin

See NestedLoopsJoin algorithms (JNL, JNLFO, JNLO).

JNLO Left Outer NestedLoopsJoin

See NestedLoopsJoin algorithms (JNL, JNLFO, JNLO).

JNLS NestedLoopsSemijoin

See NestedLoopsSemijoin algorithm (JNLS).

KEYSET Keyset

Indicates a keyset-driven cursor. See SQL Anywhere cursors.

LOAD Load

Root node of a load operation. See LOAD TABLE statement.

MultiIdx MultipleIndexScan

See MultipleIndexScan method (MultIdx).

OpenString OpenString

See OpenString algorithm (OpenString).

Optimization Time

The total time spent by the optimizer during all enumeration processes for a given statement.

PC ProcCall

Procedure call (table function). See ProcCall algorithm (PC).

PreFilter PreFilter

See Filter algorithms (Filter, PreFilter).

RL RowLimit

See RowLimit algorithm (RL).

ROWID RowIdScan

In a graphical plan, a row ID scan appears as a table name in a rectangle. See RowIdScan method (ROWID).

ROWS RowConstructor

See RowConstructor algorithm (ROWS).

RR RowReplicate

See RowReplicate algorithm (RR).

RT RecursiveTable

See RecursiveTable algorithm (RT).

RU RecursiveUnion

See RecursiveUnion algorithm (RU).

SELECT Select

Root node of a SELECT operation. See SELECT statement.

seq TableScan, ParallelTableScan

In a graphical plan, table scans appear as a table name in a rectangle. See TableScan method (seq), and ParallelTableScan method.

Sort Sort

Indexed or merge sort. See Sort algorithm (Sort).

SrtN SortTopN

See SortTopN algorithm (SrtN).

TermBreak TermBreak

The full text search termbreaker algorithm. See Alter a text index.

UA UnionAll

See UnionAll algorithm (UA).

UPDATE Update

The root node of an UPDATE operation. See UPDATE statement.

Window Window

See Window algorithm (Window).

Work Work table

An internal node that represents an intermediate result.

Common statistics used in the plan

The following statistics are actual, measured amounts.

Statistic Explanation
Invocations Number of times a row was requested from the sub tree.
RowsReturned Number of rows returned for the current node.
RunTime Time required for execution of the sub-tree, including time for children.
CacheHits Number of successful reads of the cache.
CacheRead Number of database pages that have been looked up in the cache.
CacheReadTable Number of table pages that have been read from the cache.
CacheReadIndLeaf Number of index leaf pages that have been read from the cache.
CacheReadIndInt Number of index internal node pages that have been read from the cache.
DiskRead Number of pages that have been read from disk.
DiskReadTable Number of table pages that have been read from disk.
DiskReadIndLeaf Number of index leaf pages that have been read from disk.
DiskReadIndInt Number of index internal node pages that have been read from disk.
DiskWrite Number of pages that have been written to disk (work table pages or modified table pages).
IndAdd Number of entries that have been added to indexes.
IndLookup Number of entries that have been looked up in indexes.
FullCompare Number of comparisons that have been performed beyond the hash value in an index.
Common estimates used in the plan
Statistic Explanation
EstRowCount Estimated number of rows that the node will return each time it is invoked.
AvgRowCount Average number of rows returned on each invocation. This is not an estimate, but is calculated as RowsReturned / Invocations. If this value is significantly different from EstRowCount, the selectivity estimates may be poor.
EstRunTime Estimated time required for execution (sum of EstDiskReadTime, EstDiskWriteTime, and EstCpuTime).
AvgRunTime Average time required for execution (measured).
EstDiskReads Estimated number of read operations from the disk.
AvgDiskReads Average number of read operations from the disk (measured).
EstDiskWrites Estimated number of write operations to the disk.
AvgDiskWrites Average number of write operations to the disk (measured).
EstDiskReadTime Estimated time required for reading rows from the disk.
EstDiskWriteTime Estimated time required for writing rows to the disk.
EstCpuTime Estimated processor time required for execution.
Items in the plan related to SELECT, INSERT, UPDATE, and DELETE
Item Explanation
Optimization Goal Determines whether query processing is optimized towards returning the first row quickly, or minimizing the cost of returning the complete result set. See optimization_goal option [database].
Optimization workload Determines whether query processing is optimized towards a workload that is a mix of updates and reads or a workload that is predominantly read-based. See optimization_workload option [database].
ANSI update constraints Controls the range of updates that are permitted (options are Off, Cursors, and Strict). See ansi_update_constraints option [compatibility]
Optimization level Reserved.
Select list List of expressions selected by the query.
Materialized views

List of materialized views considered by the optimizer. Each entry in the list is a tuple in the following format: view-name [ view-matching-outcome ] [ table-list ] where view-matching-outcome reveals the usage of a materialized view; if the value is COSTED, the view was used during enumeration. The table-list is a list of query tables that were potentially replaced by this view.

Values for view-matching-outcome include:

  • Base table mismatch
  • Permissions mismatch
  • Predicate mismatch
  • Select list mismatch
  • Costed
  • Stale mismatch
  • Snapshot stale mismatch
  • Cannot be used by optimizer
  • Cannot be used internally by optimizer
  • Cannot build definition
  • Cannot access
  • Disabled
  • Options mismatch
  • Reached view matching threshold
  • View used

For more information about restrictions and conditions that prevent the optimizer from using a materialized view, see Improving performance with materialized views, and Restrictions on materialized views.

Items in the plan related to locks
Item Explanation
Locked tables List of all locked tables and their isolation levels.
Items in the plan related to scans
Item Explanation
Table name Actual name of the table.
Correlation name Alias for the table.
Estimated rows Estimated number of rows in the table.
Estimated pages Estimated number of pages in the table.
Estimated row size Estimated row size for the table.
Page maps YES when a page map is used to read multiple pages.
Items in the plan related to index scans
Item Explanation
Selectivity Estimated number of rows that match the range bounds.
Index name Name of the index.
Key type Can be one of PRIMARY KEY, FOREIGN KEY, CONSTRAINT (unique constraint), or UNIQUE (unique index). The key type does not appear if the index is a non-unique secondary index.
Depth Height of the index. See Table and page sizes.
Estimated leaf pages Estimated number of leaf pages.
Sequential Transitions Statistics for each physical index indicating how clustered the index is.
Random Transitions Statistics for each physical index indicating how clustered the index is.
Key Values The number of unique entries in the index.
Cardinality Cardinality of the index if it is different from the estimated number of rows. This applies only to SQL Anywhere databases version 6.0.0 and earlier.
Direction FORWARD or BACKWARD.
Range bounds Range bounds are shown as a list (col_name=value) or col_name IN [low, high].
Primary Key Table The primary key table name for a foreign key index scan.
Primary Key Table Estimated Rows The number of rows in the primary key table for a foreign key index scan.
Primary Key Column The primary key column names for a foreign key index scan.
Items in the plan related to joins, filter, and pre-filter
Item Explanation
Predicate Search condition that is evaluated in this node, along with selectivity estimates and measurement. See Viewing selectivity in the graphical plan
Items in the plan related to hash filter
Item Explanation
Build values Estimated number of distinct values in the input.
Probe values Estimated number of distinct values in the input when checking the predicate.
Bits Number of bits selected to build the hash map.
Pages Number of pages required to store the hash map.
Items in the plan related to Union
Item Explanation
Union List Columns involved in a UNION statement.
Items in the plan related to GROUP BY
Item Explanation
Aggregates All the aggregate functions.
Group-by list All the columns in the group by clause.
Items in the plan related to DISTINCT
Item Explanation
Distinct list All the columns in the distinct clause.
Items in the plan related to IN LIST
Item Explanation
In List All the expressions in the specified set.
Expression SQL Expressions to compare to the list.
Items in the plan related to SORT
Item Explanation
Order-by List of all expressions to sort by.
Items in the plan related to row limits
Item Explanation
Row limit count Maximum number of rows returned as specified by FIRST or TOP n.