Reading access plans

The optimizer can tell you the query optimization strategy (plan) it has chosen in response to any statement.

The optimizer's job is to understand the semantics of your query and to construct a plan that computes its result. This plan may not correspond exactly to the syntax you used. The optimizer is free to rewrite your query in any semantically equivalent form.

For more information about the rules Adaptive Server Anywhere obeys when rewriting your query, see and .

For information about the methods that the optimizer uses to implement your query, see .

You can view the plan in Interactive SQL or using SQL functions. You can choose to retrieve the access plan in several different formats:

As well, you can obtain plans for SQL queries with a particular cursor type.

For more information about how to access the plan, see . For information about how to read plans, see and .

Following is an explanation of the statistics and other items that are displayed in access plans.

Abbreviations used in the plan 

Following are the abbreviations that are used in short plan, and in the short name form of the graphical plans:

Name Short Plan / Short name
Hash except all EAH
Hash except EH
Hash group by GrByH
Hash table scan HTS
Clustered Hash group by GrByHClust
Hash rollup group by GrByHR
Ordered group by GrByO
Ordered rollup group by GrByOR
Single row group by GrByS
Indexed group by GrByI
Hash distinct DistH
Indexed distinct DistI
Ordered distinct DistO
Sort Top N StrN
Hash filter HF
Hash intersect all IAH
Hash intersect IH
Exists join JE
Nested loops semijoin JNLS
Hash exists JHE
Hash not exists JHNE
Hash join JH
Sorted block SrtBl
Left outer hash join JHO
Full outer hash join JHFO
Recursive hash join JHR
Left outer recursive hash join JHRO
Nested block join JNB
Left outer nested block join JNBO
Not exists join JNE
Nested loops join JNL
Left outer nested loops join JNLO
Full outer nested loops join JNLFO
Merge join JM
Left outer merge join JMO
Full outer merge join JMFO
Merge except EM
Merge except all EAM
Merge intersect IM
Merge intersect all IAM
Row limit RL
Row replicate RR
Derived table DT
Recursive table RT. In short plan is rt<seq>
Recursive union RU
Union all UA
Table scan In short plan is tablename<seq>. In graphical plans is just the table name.
Index scan In short plan is tablename<indexname>. In graphical plans is just the table name.
In list IN

For an explanation of the algorithms, see .

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 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 .
ANSI update constraints Controls the range of updates that are permitted (options are OFF, CURSORS, and STRICT).

See

Optimization level Reserved for future use.
Select list List of expressions selected by the query.

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
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 is not displayed if the index is a non-unique secondary index.
Depth Height of the index.

For more information, see .
Estimated leaf pages Estimated number of leaf pages.
Cardinality The cardinality of the index if it is different from the estimated number of rows. This applies only to Adaptive Server Anywhere databases version 6.0 and earlier.
Selectivity The estimated number of rows that match the range bounds.
Direction FORWARD or BACKWARD.
Range bounds Range bounds are shown as a list (col_name=value) or col_name IN [low, high].

Items in the plan related to joins, filter, and pre-filter 

Item Explanation
Predicate The search condition that is evaluated in this node, along with selectivity estimates and measurement.

For more information, see

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 The columns involved in a UNION operation.

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.