The Adaptive Server optimizer attempts to find the most efficient access path to your data for each table in a query by estimating the cost of the physical I/O needed to access the data, and the number of times each page must 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:
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 an index to return data, without reading the actual data rows, requiring only a fraction of the page reads required for a table scan.
Using the appopriate indexes on tables should allow most queries to access the data they need with a minimum number of page reads.
