The query optimizer checks whether the index contains all columns necessary to satisfy the query without accessing the data row, and uses a covered index scan if this is the case. However, if the index does not cover the query, the table is accessed through a row ID lookup of the data pages during the index scan.
Shown below are examples of clauses that can be fully optimized. If there are statistics on these columns, they can be used to help estimate the number of rows the query will return. If there are indexes on the columns, the indexes can be used to access the data.
au_lname = “Bennett” price >= $12.00 advance > $10000 and advance < $20000 au_lname like "Ben%" and price > $12.00
A row filtering estimate on the following single attribute predicates is made if the histogram is available on the respective attributes advance and au_lname. However, these predicates are not optimized as limiting SARGs unless a function index is built on them, since SARGs cannot have operations involving the column name.
advance * 2 = 5000 /*expression on column side not permitted */ substring(au_lname,1,3) = "Ben" /* function on column name */
However, the two clauses above can be optimized as SARGs if they are rewritten in this form:
advance = 5000/2 au_lname like "Ben%”
Consider this query, with the only index on au_lname:
select au_lname, au_fname, phone from authors where au_lname = “Gerland” and city = “San Francisco”
SARGs provide a performance advantage since they can be evaluated deep in the data manager directly on the data or index page, whereas other, more complex, expression predicates need extra processing for their evaluation. A limiting SARG reduces the number of rows scanned on an index; a filtering SARG does not reduce the number of rows scanned, but instead reduces the number of rows selected during the scan.
The clause on au_lname qualifies as a limiting SARG, since an index exists on this column, which can use this predicate for positioning to limit the index rows scanned.
au_lname = “Gerland”
There is an index on au_lname.
There are no functions or other operations on the column name.
The operator is a valid SARG operator.
The clause city = “San Francisco” matches all the criteria above except the first; there is no index on the city column, so this clause is considered to be a filtering SARG. In this case, the index on au_lname is used for the query. All data pages with a matching last name are brought into cache, and each matching row is examined to see if the city matches the search criteria.
