UltraLite uses direct page scans as an alternative to index scans when it is more efficient to access information directly from the database page. A direct page scan is only used after the optimizer confirms that:
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. If you need the order of rows to be predictable and deterministic, use an ORDER BY clause to get results in a consistent order. 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.
In version 10.0.0 and earlier of UltraLite, 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 recommended that you use the ORDER BY clause whenever possible.
|Send feedback about this page via email or DocCommentXchange||Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0|