One-fetch access (ACCESSTYPE='I1')
One-fetch index access requires retrieving only one row. It is the best possible access path and is chosen whenever it is available.
One-fetch index access applies
only to statements with MIN or MAX aggregate functions: the order
of the index allows a single row to give the result of the function.
One-fetch index access is a possible access path when:
- The query includes only one table.
- The query includes only one aggregate function (either MIN or MAX).
- Either no predicate or all predicates are matching predicates for the index.
- The query includes no GROUP BY clause.
- Aggregate functions are on:
- The first index column if no predicates exist
- The last matching column of the index if the last matching predicate is a range type
- The next index column (after the last matching column) if all matching predicates are equal type
Example queries that use one-fetch index scan
Assuming that an index exists on T(C1,C2,C3), each of the following queries use one-fetch index scan:
SELECT MIN(C1) FROM T;
SELECT MIN(C1) FROM T WHERE C1>5;
SELECT MIN(C1) FROM T WHERE C1>5 AND C1<10;
SELECT MIN(C2) FROM T WHERE C1=5;
SELECT MAX(C1) FROM T;
SELECT MAX(C2) FROM T WHERE C1=5 AND C2<10;
SELECT MAX(C2) FROM T WHERE C1=5 AND C2>5 AND C2<10;
SELECT MAX(C2) FROM T WHERE C1=5 AND C2 BETWEEN 5 AND 10;