Contents Index Subquery and function caching Text plans

ASA SQL User's Guide
  Query Optimization and Execution

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 Rewriting subqueries as EXISTS predicates and Semantic query transformations.

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

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 Accessing the plan. For information about how to read plans, see Text plans and Graphical plans.

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 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
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 Query execution algorithms.

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].
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 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 Table and page sizes.
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 Selectivity in the 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 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.

Text plans
Graphical plans
Accessing the plan

Contents Index Subquery and function caching Text plans