ASA SQL User's Guide
Query Optimization and Execution
Query execution algorithms
Accessing tables
A sequential table scan reads all the rows in all the pages of a table in the order in which they are stored in the database.
Sequential table scans are displayed in the short and long plan as correlation_name<seq>, where correlation_name is the correlation name specified in the FROM clause, or the table name if none was specified.
This type of scan is used when it is likely that a majority of table pages have a row that match the query's search condition or a suitable index is not defined.
Although sequential table scans may read more pages than index scans, the disk I/O can be substantially cheaper because the pages are read in contiguous blocks from the disk (this performance improvement is best if the database file is not fragmented on the disk). Sequential I/O minimizes overhead due to disk head movement and rotational latency. For large tables, sequential table scans also read groups of several pages at a time. This further reduces the cost of sequential table scans relative to index scans.
Although sequential table scans may take less time than index scans that match many rows, they also cannot exploit the cache as effectively as index scans if the scan is executed many times. Since index scans are likely to access fewer table pages, it is more likely that the pages will be available in the cache, resulting in faster access. Because of this, it is much better to have an index scan for table accesses that are repeated, such as the right hand side of a nested loops join.
For isolation level 3, Adaptive Server Anywhere acquires a lock on each row that is accessed—even if it does not satisfy the search condition. For this level, sequential table scans acquire locks on all of the rows in the table, while index scans only acquire locks on the rows that match the search condition. This means that sequential table scans may substantially reduce the throughput in multi-user environments. For this reason, the optimizer prefers indexed access over sequential access at isolation level 3. Sequential scans can efficiently evaluate simple comparison predicates between table columns and constants during the scan. Other search conditions that refer only to the table being scanned are evaluated after these simple comparisons, and this approach is slightly more efficient that evaluating the conditions in a filter after the sequential scan.