Examples of queries that return larger scan count values are:
Parallel queries that report a scan for each worker process.
Queries that have indexed where clauses connected by or report a scan for each or clause. If the query uses the special OR strategy, it reports one scan for each value. If the query uses the OR strategy, it reports one scan for each index, plus one scan for the RID list access.
This query uses the special OR strategy, so it reports a scan count of 2 if the titles table has indexes on title_id and another on pub_id:
select title_id
from titles
where title_id = "T55522"
or pub_id = "P988"
Table: titles scan count 2,logical reads: (regular=149 apf=0 total=149), physical reads: (regular=63 apf=80 total=143), apf IOs used=80
Table: Worktable1 scan count 1, logical reads: (regular=172 apf=0 total=172), physical reads: (regular=0 apf=0 total=0), apf IOs
The I/O for the worktable is also reported.
Nested-loop joins that scan inner tables once for each qualifying row in the outer table. In the following example, the outer table, publishers, has three publishers with the state “NY”, so the inner table, titles, reports a scan count of 3:
select title_id
from titles t, publishers p
where t.pub_id = p.pub_id
and p.state = "NY"
Table: titles scan count 3,logical reads: (regular=442 apf=0 total=442), physical reads: (regular=53 apf=289 total=342), apf IOs used=289 Table: publishers scan count 1, logical reads: (regular=2 apf=0 total=2), physical reads: (regular=2 apf=0 total=2), apf IOs used=0
This query performs a table scan on publishers, which occupies only 2 data pages, so 2 physical I/Os are reported. There are 3 matching rows in publishers, so the query scans titles 3 times, using an index on pub_id.
Merge joins with duplicate values in the outer table restart the scan for each duplicate value, and report an additional scan count each time.
