TableScan reads all the rows in all the pages of a table in the order in which they are stored in the database. This is known as a sequential table scan.
Sequential table scans appear in the short and long text 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.
Sequential table scans are used when it is likely that most of the 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 reduces 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 transactions executing at isolation level 3, SQL Anywhere acquires a lock on each row that is accessed—even if it does not satisfy the search condition. At this isolation level, sequential table scans acquire locks on all 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 strongly 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 than evaluating the conditions in a filter after the sequential scan.
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|