Database – Full Table Scans

A full table scan occurs when an index is either not used or there is no index on the table(s) being used by the SQL statement. Full table scans usually return data much slower than when an index is used. The larger the table, the slower that data is returned when a full table scan is performed. The query optimizer decides whether to use an index when executing the SQL statement. The index is used—if it exists—in most cases.

Some implementations have sophisticated query optimizers that can decide whether an index should be used. Decisions such as this are based on statistics that are gathered on database objects, such as the size of an object and the estimated number of rows that are returned by a condition with an indexed column. Please refer to your implementation documentation for specifics on the decision-making capabilities of your relational database's optimizer.

When and How to Avoid Full Table Scans

Full table scans should be avoided when reading large tables. For example, a full table scan is performed when a table that does not have an index is read, which usually takes a considerably longer time to return the data.

An index should be considered for most larger tables.

On small tables, as previously mentioned, the optimizer may choose the full table scan over using the index, if the table is indexed. In the case of a small table with an index, consideration should be given to dropping the index and reserving the space that was used for the index for other needy objects in the database.

Note

The easiest and most obvious way to avoid a full table scan-outside of ensuring that indexes exist on the table—is to use conditions in a query's WHERE clause to filter data to be returned.

The following is a reminder of data that should be indexed:

• Columns used as primary keys
• Columns used as foreign keys
• Columns frequently used to join tables
• Columns frequently used as conditions in a query
• Columns that have a high percentage of unique values

Note

Sometimes full table scans are good. Full table scans should be performed on queries against small tables or queries whose conditions return a high percentage of rows. The easiest way to force a full table scan is to avoid creating an index on the table.

SOURCE	LINK (informit.com)	LANGUAGE	ENGLISH