|
A direct page scan is used as an alternative to index scans. A direct page scan is used when the following conditions occur:
The UltraLite optimizer determines that no existing index can help to return results more efficiently.
The UltraLite optimizer is certain that you are not using the query to perform updates. That is, either you have declared the statement to be FOR READ ONLY, or the query is written in such a way that it is obvious that data cannot be updated.
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.
NoteYou 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 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.
NoteIt 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. |