Reading UltraLite execution plans

Because UltraLite short plans are textual summaries of how a query is accessed, you need to understand how the operations of either a join or a scan of a table are implemented.

  • For scan operations   Represented with a single operand, which applies to a single table only and uses an index. The table name and index name are displayed as round brackets ( (, ) ) following the operation name.

  • For other operations   Represented with one or more operands, which can also be plans in and of themselves. In UltraLite, these operands are comma-separated lists contained by square brackets ( [ ] ).

Operation list

Operations supported by UltraLite are listed in the table that follows.

Operation Description
count(*)

Counts the number of rows in a table.
distinct[ plan ]

Implements the DISTINCT aspect of a query to compare and eliminate duplicate rows. It is used when the underlying plan sorts rows in such a way that duplicate contiguous rows are eliminated. If two contiguous rows match, only the first row is added to the result set.

dummy

No operation performed. It only occurs in two cases:

  • When you specify DUMMY in a FROM clause.

  • When the FROM clause is missing from the query.
filter[ plan ]

Executes a search condition for each row supplied by the underlying plan. Only the rows that evaluate to true are forwarded as part of the result set.

group-by[ plan ]

Creates an aggregate of GROUP BY results, to sort multiple rows of grouped data. Rows are listed in the order they occur and are grouped by comparing contiguous rows.

group-single[ plan ]

Creates an aggregate of GROUP BY results, but only when it is known that a single row will be returned.

keyset[ plan ]

Records which rows were used to create rows in a temporary table so UltraLite can update the original rows. If you do not want those rows to be updated, then use the FOR READ ONLY clause in the query to eliminate this operation.

index-scan( table-name, index-name )

Reads only part of the table; the index is used to find the starting row.

join[ plan, plan ]

Performs an inner join between two plans.
lojoin[ plan, plan ]

Performs a left outer join between two plans.
like-scan( table-name, index-name )

Reads only part of a table; the index is used to find the starting row by pattern matching.
rowlimit[ plan ]

Performs the row limiting operation on propagated rows. Row limits are set by the TOP n or FIRST clause of the SELECT statement.
scan( table-name, index-name )

Reads an entire table following the order indicated by the index.
sub-query[ plan ]

Marks the start of a subquery.
temp[ plan ]

Creates a temporary table from the rows in the underlying plan. UltraLite uses a temporary table when underlying rows must be ordered and no index was found to do this ordering.

You can add an index to eliminate the need for a temporary table. However, each additional index used increases the duration needed to insert or synchronize rows in the table for which the index applies.

union-all[ plan, ..., plan ]

Performs a UNION ALL operation on the rows generated in the underlying plan.
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1