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 How to 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.
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.
ANSI update constraints	Controls the range of updates that are permitted (options are Off, Cursors, and Strict). See ansi_update_constraints option
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 prefilter

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.

Discuss this page in DocCommentXchange.