It is important to distinguish between where and having clause predicates that can be used to optimize the query, and those that are used later during query processing to filter the rows to be returned.
Search arguments can be used to determine the access path to the data rows when a column in the where clause matches a leading index key. The index can be used to locate and retrieve the matching data rows. Once the row has been located in the data cache or has been read into the data cache from disk, any remaining clauses are applied.
For example, if the authors table has on an index on au_lname and another on city, either index can be used to locate the matching rows for this query:
select au_lname, city, state from authors where city = "Washington" and au_lname = "Catmull"
The optimizer uses statistics, including histograms, the number of rows in the table, the index heights, and the cluster ratios for the index and data pages to determine which index provides the cheapest access. The index that provides the cheapest access to the data pages is chosen and used to execute the query, and the other clause is applied to the data rows once they have been accessed.
