Scan direction messages

Forward scan.

Backward scan.

These messages indicate the direction of a table or index scan.

The scan direction depends on the ordering specified when the indexes were created and the order specified for columns in the order by clause.

Backward scans cam be used when the order by clause contains the asc or desc qualifiers on index keys, in the exact opposite of those in the create index clause. The configuration parameter allow backward scans must be set to 1 to allow backward scans.

The scan-direction messages are followed by positioning messages. Any keys used in the query are followed by “ASC” or “DESC”. The forward and backward scan messages and positioning messages describe whether a scan is positioned:

At the first matching index key, at the start of the table, or at the first page of the leaf-level pages chain, and searching toward end of the index, or
At the last matching index key, or end of the table, or last page of the leaf-level page chain, and searching toward the beginning.

If allow backward scans is set to 0, all accesses use forward scans.

This example uses a backward scan:

select *
from sysmessages 
where description like "%Optimized using%"
order by error desc

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

        FROM TABLE
            sysmessages
        Nested iteration.
        Table Scan.
        Backward scan.
        Positioning at end of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

This query using the max aggregate also uses a backward scan:

select max(error) from sysmessages

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.
        Evaluate Ungrouped MAXIMUM AGGREGATE.

        FROM TABLE
            sysmessages
        Nested iteration.
        Index : ncsysmessages
        Backward scan.
        Positioning by key.
        Scanning only up to the first qualifying row.
        Index contains all needed columns. Base table will not be read.
        Keys are:
            error  ASC
        Using I/O Size 2 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.

    STEP 2
        The type of query is SELECT.