Matching index scans message

Using N Matching Index Scans.

This showplan message indicates that a query using or clauses or an in (values list) clause uses multiple index scans (also called the “special OR strategy”) instead of using a dynamic index.

Multiple matching scans can be used only when there is no possibility that the or clauses or in list items will match duplicate rows – that is, when there is no need to build the worktable and perform the sort to remove the duplicates.

For more information on how queries containing or are processed, see Performance and Tuning Guide: Optimizer.

For queries that use multiple matching scans, different indexes may be used for some of the scans, so the messages that describe the type of index, index positioning, and keys used are printed for each scan.

The following example uses multiple matching index scans to return rows:

select title
    from titles
    where title_id in ("T18168","T55370")
QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

        FROM TABLE
            titles
        Nested iteration.
        Using 2 Matching Index Scans
        Index : title_id_ix
        Forward scan.
        Positioning by key.
        Keys are:
            title_id
        Index : title_id_ix
        Forward scan.
        Positioning by key.
        Keys are:
            title_id
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.