Adaptive Server uses multiple matching index scans when the or clauses are on the same table, and there is no possibility that the or clauses will return duplicate rows. For example, this query cannot return any duplicate rows:
select title
from titles
where title_id in ("T6650", "T95065", "T11365")
This query can be resolved using multiple matching index scans, using the index on title_id. The total cost of the query is the sum of the multiple index accesses performed. If the index on title_id has 3 levels, each or clause requires 3 index reads, plus one data page read, so the total cost for each clause is 4 logical and 4 physical I/Os, and the total query cost is estimated to be 12 logical and 12 physical I/Os.
The optimizer determines which index to use for each or clause or value in the in (values_list) clause by costing each clause or value separately. If each column named in a clause is indexed, a different index can be used for each clause or value. showplan displays the message “Using N Matching Index Scans” when the special OR strategy is used.
See “Matching index scans message” on page 106 in the book Performance and Tuning: Monitoring and Analyzing for Performance.
