The Adaptive Server optimizer attempts to find the most efficient access path to your data for each table in the query, by estimating the cost of the physical I/O needed to access the data, and the number of times each page needs to be read while in the data cache.
In most database applications, there are many tables in the database, and each table has one or more indexes. Depending on whether you have created indexes, and what kind of indexes you have created, the optimizer’s access method options include:
A table scan – reading all the table’s data pages, sometimes hundreds or thousands of pages.
Index access – using the index to find only the data pages needed, sometimes as few as three or four page reads in all.
Index covering – using only a non clustered index to return data, without reading the actual data rows, requiring only a fraction of the page reads required for a table scan.
Having the proper set of indexes on your tables should allow most of your queries to access the data they need with a minimum number of page reads.
