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 | |
DistO | OrderedDistinct | |
DP | DecodePostings |
See DecodePostings (DP). |
DT | DerivedTable | |
EAH | HashExceptAll | |
EAM | MergeExceptAll | |
EH | HashExcept | |
EM | MergeAccept | |
Exchange | Exchange | |
Filter | Filter | |
GrByH | HashGroupBy | |
GrByHClust | HashGroupByClustered | |
GrByHSets | HashGroupBySets | |
GrByO | OrderedGroupBy | |
GrByOSets | OrderedGroupBySets | |
GrByS | SingleRowGroupBy | |
GrBySSets | SortedGroupBySets | |
HF | HashFilter | |
HFP | ParallelHashFilter | |
HTS | HashTableScan | |
IAH | HashIntersectAll | |
IAM | MergeIntersectAll | |
IH | HashIntersect | |
IM | MergeIntersect | |
IN | InList | |
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 | |
INSERT | Insert |
Root node of an insert operation. See INSERT statement. |
IO | IndexOnlyScan, ParallelIndexOnlyScan |
See IndexOnlyScan method (IO), and ParallelIndexScan method. |
JH | HashJoin | |
JHS | HashSemijoin | |
JHSP | ParallelHashSemijoin | |
JHFO | Full Outer HashJoin | |
JHA | HashAntisemijoin | |
JHAP | ParallelHashAntisemijoin | |
JHO | Left Outer HashJoin | |
JHP | ParallelHashJoin | |
JHPO | ParallelLeftOuterHashJoin | |
JHR | RecursiveHashJoin | |
JHRO | RecursiveLeftOuterHashJoin | |
JM | MergeJoin | |
JMFO | Full Outer MergeJoin | |
JMO | Left Outer MergeJoin | |
JNL | NestedLoopsJoin | |
JNLA | NestedLoopsAntisemijoin | |
JNLFO | Full Outer NestedLoopsJoin | |
JNLO | Left Outer NestedLoopsJoin | |
JNLS | NestedLoopsSemijoin | |
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 | |
OpenString | 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 | |
RL | RowLimit | |
ROWID | RowIdScan |
In a graphical plan, a row ID scan appears as a table name in a rectangle. See RowIdScan method (ROWID). |
ROWS | RowConstructor | |
RR | RowReplicate | |
RT | RecursiveTable | |
RU | RecursiveUnion | |
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 | |
TermBreak | TermBreak |
The full text search termbreaker algorithm. See Alter a text index. |
UA | UnionAll | |
UPDATE | Update |
The root node of an UPDATE operation. See UPDATE statement. |
Window | Window | |
Work | Work table |
An internal node that represents an intermediate result. |
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. |
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. |
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: Values for view-matching-outcome include:
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. |
Item | Explanation |
---|---|
Locked tables | List of all locked tables and their isolation levels. |
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. |
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. |
Item | Explanation |
---|---|
Predicate | Search condition that is evaluated in this node, along with selectivity estimates and measurement. See Viewing selectivity in the graphical plan |
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. |
Item | Explanation |
---|---|
Union List | Columns involved in a UNION statement. |
Item | Explanation |
---|---|
Aggregates | All the aggregate functions. |
Group-by list | All the columns in the group by clause. |
Item | Explanation |
---|---|
Distinct list | All the columns in the distinct clause. |
Item | Explanation |
---|---|
In List | All the expressions in the specified set. |
Expression SQL | Expressions to compare to the list. |
Item | Explanation |
---|---|
Order-by | List of all expressions to sort by. |
Item | Explanation |
---|---|
Row limit count | Maximum number of rows returned as specified by FIRST or TOP n. |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |