Queries with a known search condition Access pathwhen optimizer's cost estimates for it are accurate

When the exact start and stop conditions are known at compilation time, the optimizer uses the index itself to make a very precise estimate of the number of rows that will be scanned from disk. An example of a query with a known search condition:

SELECT * FROM Flights WHERE orig_airport = 'SFO'

The search value, 'SFO', is known. The optimizer will be able to make an accurate estimate of the cost of using the index orig_index.

In addition, if the index is unique, and the WHERE clause involves an = or IS NULL comparison to all the columns in the index, the optimizer knows that only a single row will be scanned from disk. For example: -- there's a unique key on city_id SELECT * FROM Cities WHERE city_id = 1