Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.1 » SQL Anywhere Server - SQL Usage » Query and modify data » Queries » Advanced: Query execution plans

 

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
 Node Statistics field descriptions
 Common statistics used in the plan
 Common estimates used in the plan
 Items in the plan related to SELECT, INSERT, UPDATE, and DELETE
 Items in the plan related to locks
 Items in the plan related to scans
 Items in the plan related to index scans
 Items in the plan related to joins, filter, and prefilter
 Items in the plan related to hash filter
 Items in the plan related to Union
 Items in the plan related to GROUP BY
 Items in the plan related to DISTINCT
 Items in the plan related to IN LIST
 Items in the plan related to SORT
 Items in the plan related to row limits
 See also