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.
