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 Performance and Optimization » Optimizing UltraLite query performance

Managing temporary tables Next Page

Using direct page scans

A direct page scan is used as an alternative to index scans. A direct page scan is used when the following conditions occur:

Because UltraLite reads the rows directly from the pages on which the rows are stored, query results are returned without order. The order of subsequent query results is unpredictable. Therefore, if you need the order of rows to be predictable and deterministic, use an ORDER BY clause to yield consistent results. On the other hand, if order is not important, you can omit the ORDER BY clause to improve query performance.


You cannot use direct page scans if you are using the Table API to program your application.

You can check to see when UltraLite scans a page directly or which index was used to return results. See Determining the access method used by the optimizer.

Reverting to primary key index order

In versions of UltraLite previous to 10.0.1, the primary key was used to return results when no other index was used by the UltraLite optimizer. As a result, rows were ordered according to the order of the primary key index.

If your results must be ordered by primary key, you should re-write your queries to include the ORDER BY clause. If it is impractical to sort your rows with this clause, you can consider using the ORDERED_TABLE_SCAN connection parameter.


It is always recommended that you use the ORDER BY clause if at all possible. Otherwise, you cannot take advantage of the performance benefits offered by this UltraLite feature.

See also