Key-first scan

This topic shows a sample query that uses a key-first scan, which is an index scan that uses keys other than those listed as lower and upper index filters.

Figure 1: Partial SET EXPLAIN output for a key-first scan 
create index idx1 on tab1(c1, c2);

select * from tab1 where (c1 > 0) and ( (c2 = 1) or (c2 = 2))
Estimated Cost: 4
Estimated # of Rows Returned: 1

1) pubs.tab1: INDEX PATH

    (1) Index Keys: c1 c2 (Key-First) (Serial, fragments: ALL)
		 Lower Index Filter: pubs.tab1.c1 > 0
		 Index Key Filters:  (pubs.tab1.c2 = 1 OR pubs.tab1.c2 = 2)

Even though in this example the database server must eventually read the row data to return the query results, it attempts to reduce the number of possible rows by applying additional key filters first. The database server uses the index to apply the additional filter, c2 = 1 OR c2 = 2, before it reads the row data.