Execution plan components

Following are the abbreviations that you see in execution plans.

Short text plan	Long text plan	Additional information
	Costed Best Plans	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.
	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.
**	**	A complete index scan. The index scan reads all rows.
DELETE	Delete	The root node of a DELETE operation.
DistH	HashDistinct	HashDistinct takes a single input and returns all distinct rows.
DistO	OrderedDistinct	OrderedDistinct reads each row and compares it to the previous row. If it is the same, it is ignored; otherwise, it is output.
DP	DecodePostings	DecodePostings decodes positional information for the terms in the text index.
DT	DerivedTable	DerivedTable may appear in a plan due to query rewrite optimizations and a variety of other reasons, particularly when the query involves one or more outer joins.
EAH	HashExceptAll	Indicates that a hash-based implementation of the set difference SQL operator, EXCEPT, was used.
EAM	MergeExceptAll	Indicates that a sort-based implementation of the set difference SQL operator, EXCEPT, was used.
EH	HashExcept	Indicates that a hash-based implementation of the set difference SQL operator, EXCEPT, was used.
EM	MergeExcept	Indicates that a sort-based implementation of the set difference SQL operator, EXCEPT, was used.
Exchange	Exchange	Indicates that intra-query parallelism was used when processing a SELECT statement.
Filter	Filter	Indicates the application of search conditions including any type of predicate, comparisons involving subselects, and EXISTS and NOT EXISTS subqueries (and other forms of quantified subqueries).
GrByH	HashGroupBy	HashGroupBy builds an in-memory hash table containing one row per group. As input rows are read, the associated group is looked up in the work table. The aggregate functions are updated, and the group row is rewritten to the work table. If no group record is found, a new group record is initialized and inserted into the work table.
GrByHClust	HashGroupByClustered	Sometimes values in the grouping columns of the input table are clustered, so that similar values appear close together. ClusteredHashGroupBy exploits this clustering.
GrByHP	ParallelHashGroupBy	A variant of HashGroupBy.
GrByHSets	HashGroupBySets	A variant of HashGroupBy, HashGroupBySets is used when performing GROUPING SETS queries.
GrByO	OrderedGroupBy	OrderedGroupBy reads an input that is ordered by the grouping columns. As each row is read, it is compared to the previous row. If the grouping columns match, then the current group is updated; otherwise, the current group is output and a new group is started.
GrByOSets	OrderedGroupBySets	A variant of OrderedGroupBy, OrderedGroupBySets is used when performing GROUPING SETS queries.
GrByS	SingleRowGroupBy	When no GROUP BY is specified, SingleRowGroupBy is used to produce a single row aggregate. A single group row is kept in memory and updated for each input row.
GrBySSets	SortedGroupBySets	SortedGroupBySets is used when processing OLAP queries that contain GROUPING SETS.
HF	HashFilter	Indicates that a hash filter (or bloom filter) was used.
HFP	ParallelHashFilter	Indicates that a hash filter (or bloom filter) was used.
HTS	HashTableScan	Indicates that a hash table scan was used.
IAH	HashIntersectAll	Indicates that a hash-based implementation of the set difference SQL operator, INTERSECT, was used.
IAM	MergeIntersectAll	Indicates that a sort-based implementation of the set difference SQL operator, INTERSECT, was used.
IH	HashIntersect	Indicates that a hash-based implementation of the set difference SQL operator, INTERSECT, was used.
IM	MergeIntersect	Indicates that a sort-based implementation of the set difference SQL operator, INTERSECT, was used.
IN	InList	InList is used when an IN-list predicate can be satisfied using an index.
table-name<index-name>	IndexScan, ParallelIndexScan	In a graphical plan, an index scan appears as an index name in a trapezoid.
INSENSITIVE	Insensitive
INSERT	Insert	Root node of an INSERT operation.
IO	IndexOnlyScan, ParallelIndexOnlyScan	Indicates that the optimizer used an index that contained all the data that was required to satisfy the query.
JH	HashJoin	HashJoin builds an in-memory hash table of the smaller of its two inputs, and then reads the larger input and probes the in-memory hash table to find matches, which are written to a work table. If the smaller input does not fit into memory, HashJoin partitions both inputs into smaller work tables. These smaller work tables are processed recursively until the smaller input fits into memory.
JHS	HashSemijoin	HashSemijoin performs a semijoin between the left side and the right side.
JHSP	ParallelHashSemijoin	A variant of HashJoin.
JHFO	Full Outer HashJoin	A variant of HashJoin.
JHA	HashAntisemijoin	HashAntisemijoin performs an anti-semijoin between the left side and the right side.
JHAP	ParallelHashAntisemijoin	A variant of HashJoin.
JHO	Left Outer HashJoin	A variant of HashJoin.
JHP	ParallelHashJoin	A variant of HashJoin.
JHPO	ParallelLeftOuterHashJoin	A variant of HashJoin.
JHR	RecursiveHashJoin	A variant of HashJoin.
JHRO	RecursiveLeftOuterHashJoin	A variant of HashJoin.
JM	MergeJoin	MergeJoin reads two inputs that are both ordered by the join attributes. For each row of the left input, the algorithm reads all the matching rows of the right input by accessing the rows in sorted order.
JMFO	Full Outer MergeJoin	A variant of MergeJoin.
JMO	Left Outer MergeJoin	A variant of MergeJoin.
JNL	NestedLoopsJoin	NestedLoopsJoin computes the join of its left and right sides by completely reading the right side for each row of the left side.
JNLA	NestedLoopsAntisemijoin	NestedLoopsAntisemijoin joins its inputs by scanning the right side for each row of the left side.
JNLFO	Full Outer NestedLoopsJoin	A variant of NestedLoopsJoin.
JNLO	Left Outer NestedLoopsJoin	A variant of NestedLoopsJoin.
JNLS	NestedLoopsSemijoin	NestedLoopsSemijoin joins its inputs by scanning the right side for each row of the left side.
KEYSET	Keyset	Indicates a keyset-driven cursor.
LOAD	Load	Root node of a load operation.
MultiIdx	MultipleIndexScan	MultipleIndexScan is used when more than one index can or must be used to satisfy a query that contains a set of search conditions that are combined with the logical operators AND or OR.
OpenString	OpenString	OpenString is used when the FROM clause of a SELECT statement contains an OPENSTRING clause.
	Optimization Time	The total time spent by the optimizer during all enumeration processes for a given statement.
PC	ProcCall	Procedure call (table function).
PreFilter	PreFilter	Filters apply search conditions including any type of predicate, comparisons involving subselects, and EXISTS and NOT EXISTS subqueries (and other forms of quantified subqueries).
R	R	A reverse index scan. The index scan reads rows from the index in reverse order.
RL	RowLimit	RowLimit returns the first n rows of its input and ignores the remaining rows. Row limits are set by the TOP n or FIRST clause of the SELECT statement.
ROWID	RowIdScan	In a graphical plan, a row ID scan appears as a table name in a rectangle.
ROWS	RowConstructor	RowConstructor is a specialized operator that creates a virtual row for use as the input to other algorithms.
RR	RowReplicate	RowReplicate is used during the execution of set operations such as EXCEPT ALL and INTERSECT ALL.
RT	RecursiveTable	Indicates that a recursive table was used as a result of a WITH clause within a query, where the WITH clause was used for recursive union queries
RU	RecursiveUnion	RecursiveUnion is employed during the execution of recursive union queries.
SELECT	Select	Root node of a SELECT operation.
seq	TableScan, ParallelTableScan	In a graphical plan, table scans appear as a table name in a rectangle.
Sort	Sort	Indexed or merge sort.
SrtN	SortTopN	SortTopN is used for queries that contain a TOP N clause and an ORDER BY clause.
TermBreak	TermBreak	The full text search TermBreaker algorithm.
UA	UnionAll	UnionAll reads rows from each of its inputs and outputs them, regardless of duplicates. This algorithm is used to implement UNION and UNION ALL statements.
UPDATE	Update	The root node of an UPDATE operation.
Window	Window	Window is used when evaluating OLAP queries that employ window functions.
Work	Work table	An internal node that represents an intermediate result.

Optimizer Statistics field descriptions

Below are descriptions of the fields displayed in the Optimizer Statistics section of a graphical plan. Optimizer Statistics provide information about the state of the database server and about the optimization of the selected statement.

Field	Description
Optimization Method	The algorithm used to choose an execution strategy. Values returned: Bypass costed Bypassed costed simple Bypass heuristic Bypassed then optimized Optimized Reused Reused (simple)
Costed Best Plans	When the query optimizer enumerates different query execution strategies, it tracks the number of times it finds a strategy whose estimated cost is less expensive than the best strategy found before the current one. It is difficult to predict how often this will occur for any particular query, but a lower number indicates significant pruning of the search space by the optimizer's algorithms, and, typically, faster optimization times. 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. If the values for Costed Best Plans, Costed Plans, and Optimization time are 0, then the statement was not optimized by the SQL Anywhere optimizer. Instead, the database server bypassed the statement and generated the execution plan without optimizing the statement, or the plan for the statement was cached.
Costed Plans	The number of different access plans considered by the optimizer for this request whose costs were partially or fully estimated. As with Costed Best Plans, smaller values normally indicate faster optimization times and larger values indicate more complex SQL queries. If the values for Costed Best Plans, Costed Plans, and Optimization Time are 0, then the statement was not optimized. Instead, the database server bypassed the statement and generated the execution plan without optimizing the statement.
Optimization Time	The elapsed time spent optimizing the statement. If the values for Costed Best Plans, Costed Plans, and Optimization Time are 0, then the statement was not optimized. Instead, the database server bypassed the statement and generated the execution plan without optimizing the statement.
Estimated Cache Pages	The estimated current cache size available for processing the statement. To reduce inefficient access plans, the optimizer assumes that one-half of the current cache size is available for processing the selected statement.
CurrentCacheSize	The database server's cache size in kilobytes at the time of optimization.
QueryMemMaxUseful	The number of pages of query memory that are useful for this request. If the number is zero, then the statement's execution plan contains no memory-intensive operators and is not subject to control by the server's memory governor.
QueryMemNeedsGrant	Indicates whether the memory governor must grant memory to one or more memory-intensive query execution operators that are present in this request's execution strategy.
QueryMemLikelyGrant	The estimated number of pages from the query memory pool that would be granted to this statement if it were executed immediately. This estimate can vary depending on the number of memory-intensive operators in the plan, the database server's multiprogramming level, and the number of concurrently-executing memory-intensive requests.
QueryMemPages	The total amount of memory in the query memory pool that is available for memory-intensive query execution algorithms for all connections, expressed as a number of pages.
QueryMemActiveMax	The maximum number of tasks that can actively use query memory at any particular time.
QueryMemActiveEst	The database server's estimate of the steady state average of the number of tasks actively using query memory.
isolation_level	The isolation level of the statement. The isolation level of the statement may differ from other statements in the same transaction, and may be further overridden for specific base tables through the use of hints in the FROM clause.
optimization_goal	Indicates if query processing is optimized for returning the first row quickly, or minimizing the cost of returning the complete result set.
optimization_level	Controls amount of effort made by the query optimizer to find an access plan.
optimization_workload	The Mixed or OLAP value of the optimization_workload setting.
max_query_tasks	Maximum number of tasks that may be used by a parallel execution plan for a single query.
user_estimates	Controls whether to respect or ignore user estimates that are specified in individual predicates in the query text.

Node Statistics field descriptions

Below are descriptions of the fields displayed in the Node Statistics section of a graphical plan.

Field	Description
CacheHits	The total number of cache read requests by this operator which were satisfied by the buffer pool that did not require a disk read operation.
CacheRead	Total number of attempts made by this operator to read a page of the database file, typically for table and/or index pages.
CPUTime	The CPU time incurred by the processing algorithm represented by this node.
DiskRead	The cumulative number of pages that have been read from disk as a result of this node's processing.
DiskReadTime	The cumulative elapsed time required to perform disk reads for database pages required by this node for processing.
DiskWrite	The commutative number of pages that have been written to disk as a result of this node's processing.
DiskWriteTime	The cumulative elapsed time required to perform disk writes for database pages as required by this node's processing algorithm.
FirstRowRunTime	The FirstRowRunTime value is the actual elapsed time required to produce the first row of the intermediate result of this node.
Invocations	The number of times the node was called to compute a result, and return that result to the parent node. Most nodes are called only once. However, if the parent of a scan node is a nested loops join, then the node might be executed multiple times, and could possibly return a different set of rows after each invocation.
PercentTotalCost	The RunTime spent computing the result within this particular node, expressed as a percentage of the total RunTime for the statement.
QueryMemMaxUseful	The estimated amount of query memory that is expected to be used for this particular operator. If the actual amount of query memory used, which is reported as the Actual statistic, differs significantly then it may indicate a potential problem with result set size estimation by the query optimizer. A probable cause of this estimation error is inaccurate or missing predicate selectivity estimates.
RowsReturned	The number of rows returned to the parent node as a result of processing the request. RowsReturned is often, but not necessarily, identical to the number of rows in the (possibly derived) object represented by that node. Consider a leaf node that represents a base table scan. It is possible for the RowsReturned value to be smaller or larger than the number of rows in the table. RowsReturned are smaller if the parent node fails to request all the table's rows in computing the final result. RowsReturned may be greater in a case such as a GROUP BY GROUPING SETS query, where the parent Group By Hash Grouping Sets node requires multiple passes over the input to compute the different groups. A significant difference between the estimated rows returned and the actual number returned could indicate that the optimizer might be operating with poor selectivity information.
RunTime	This value is a measure of wall clock time, including waits for input/output, row locks, table locks, internal server concurrency control mechanisms, and actual runtime processing. The interpretation of RunTime depends on the statistics section in which it appears. In Node Statistics, RunTime is the cumulative time the node's corresponding operator spent during execution for this node alone. Both estimated and actual values for this statistic appear in the Node Statistics section. If a node's RunTime is greater than expected for a table scan or index scan, then further analysis may help pinpoint the problem. The query may be contending for shared resources and may block as a result; you can monitor blocked connections using the sa_locks() system procedure. As another example, the database page layout on the disk may be suboptimal, or a table may suffer from internal page fragmentation. You may improve performance by executing the REORGANIZE TABLE statement. You can use the sa_table_fragmentation() and the sa_index_density() system procedures to determine whether the table or index are fragmented.

Common statistics used in the plan

The following statistics are actual, measured amounts.

Statistic	Explanation
CacheHits	Returns the number of database page lookups satisfied by finding the page in the cache.
CacheRead	Returns the number of database pages that have been looked up in the cache.
CacheReadTable	Returns the number of table pages that have been read from the cache.
CacheReadIndLeaf	Returns the number of index leaf pages that have been read from the cache.
CacheReadIndInt	Returns the number of index internal-node pages that have been read from the cache.
DiskRead	Returns the number of pages that have been read from disk.
DiskReadTable	Returns the number of table pages that have been read from disk.
DiskReadIndLeaf	Returns the number of index leaf pages that have been read from disk.
DiskReadIndInt	Returns the number of index internal-node pages that have been read from disk.
DiskWrite	Returns the number of modified pages that have been written to disk.
IndAdd	Returns the number of entries that have been added to indexes.
IndLookup	Returns the number of entries that have been looked up in indexes.
FullCompare	Returns the 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.
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.
ANSI update constraints	Controls the range of updates that are permitted (options are Off, Cursors, and Strict).
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

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.
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.

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.