When a query requires a table scan, Adaptive Server reads each page of the table from disk into the data cache and checks the data values (if there is a where clause) and returns qualifying rows.
Table scans are performed:
When no index exists on the columns used in the search clauses.
When the optimizer determines that using the index is more expensive than performing a table scan. The optimizer may determine that it is cheaper to read the data pages directly than to read the index pages and then the data pages for each row that is to be returned.
The cost of a table scan depends on the size of the table and the I/O size.
Reference to Large I/Os are on a 2K logical
page size server. If you have an 8K page size server, the basic
unit for the I/O is 8K. If you have a 16K page size server,
the basic unit for the I/O is 16K.
