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: Optimizing and Abstract Plans.
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.