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

SQL Anywhere 10.0.1 » UltraLite - Database Management and Reference » UltraLite SQL Elements Reference » Query access plans in UltraLite

Viewing an UltraLite query access plan Next Page

Reading UltraLite access 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.

Operation list

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


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.


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, in order 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 accomplish 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 to which the index applies.

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

Performs a UNION ALL operation on the rows generated in the underlying plan.