Using direct page scans

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:

No pre-existing index can return results more efficiently.
You are not using the query to perform updates. For example, you have declared the statement to be FOR READ ONLY (the default setting if no FOR clause has been specified), or have written the query in such a way that it is obvious that data is not being 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. 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.

Note

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 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.

Tip

It is recommended that you use the ORDER BY clause whenever possible.