ASA SQL User's Guide
Query Optimization and Execution
Query execution algorithms
Accessing tables
An index scan uses an index to determine which rows satisfy a search condition. It reads only those pages that satisfy the condition. Indexes can return rows in sorted order.
Index scans are displayed in the short and long plan as correlation_name<index_name>, where correlation_name is the correlation name specified in the FROM clause, or the table name if none was specified; and index_name is the name of the index.
Indexes provide an efficient mechanism for reading a few rows from a large table. However, index scans cause pages to be read from the database in random order, which is more expensive than sequential reads. Index scans may also reference the same table page multiple times if there are several rows on the page that satisfy the search condition. If only a few pages are matched by the index scan, it is likely that the pages will remain in cache, and multiple access does not lead to extra I/O. However, if many pages are matched by the search condition, they may not all fit in cache. This can lead to the index scan reading the same page from disk multiple times.
The optimizer will tend to prefer index scans over sequential table scans if the OPTIMIZATION_GOAL setting is first-row. This is because indexes tend to return the first few rows of a query faster than table scans.
Indexes can also be used to satisfy an ordering requirement, either explicitly defined in an ORDER BY clause, or implicitly needed for a GROUP BY or DISTINCT clause. Ordered group-by and ordered distinct methods can return initial rows faster than hash-based grouping and distinct, but they may be slower at returning the entire result set.
The optimizer uses an index scan to satisfy a search condition if the search condition is sargable, and if the optimizer's estimate of the selectivity of the search condition is sufficiently low for the index scan to be cheaper than a sequential table scan.
An index scan can also evaluate non-sargable search conditions after rows are fetched from the index. Evaluating conditions in the index scan is slightly more efficient than evaluating them in a filter after the index scan.
For more information about when Adaptive Server Anywhere can make use of indexes, see Predicate analysis.
For more information about optimization goals, see OPTIMIZATION_GOAL option [database].