Index contains all needed columns. Base table will not be read.
This message indicates that an index covers the query. It is printed both for matching and nonmatching scans. Other messages in showplan output help distinguish these access methods:
A matching scan reports “Positioning by key.”
A nonmatching scan reports “Positioning at index start,” or “Positioning at index end” since a nonmatching scan must read the entire leaf level of the index.
If the optimizer uses a matching scan, the “Keys are...” message reports the keys used to position the search. This message is not included for a nonmatching scan.
The next query shows output for a matching scan, using a composite, nonclustered index on au_lname, au_fname, au_id:
select au_fname, au_lname, au_id from authors where au_lname = "Williams"
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
authors
Nested iteration.
Index : au_names_id
Forward scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
au_lname ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
With the same composite index on au_lname, au_fname, au_id, this query performs a nonmatching scan, since the leading column of the index is not included in the where clause:
select au_fname, au_lname, au_id from authors where au_id = "A93278"
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
authors
Nested iteration.
Index : au_names_id
Forward scan.
Positioning at index start.
Index contains all needed columns. Base table will not be read.
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Note that the showplan output does not contain a “Keys are...” message, and the positioning message is “Positioning at index start.” This query scans the entire leaf level of the nonclustered index, since the rows are not ordered by the search argument.
