Database Related

What Is A Full Table Scan In Databases?

A full table scan, scans one by one all the rows present in a table for finding the data that a query is searching for. Apparently, this results in very slow SQL queries when you have a table having multiple rows.

Now you can even imagine that how performance-rigorous a complete table scan would be on a table having millions of rows. However, you can help prevent full table scans by using an index. Following are a few scenarios which result in a complete table scan:

When Statistics Are Not Updated

Generally, statistics are kept on indexes and tables. However, a full table scan will happen, if for any reason index or table statistics have not been updated. The major reason behind this is that most RDBMS’s comprises of query optimizers that utilize those statistics for figuring out whether using an index is useful. And when those statistics are not present, then the RDBMS may mistakenly determine that doing a complete table scan is more beneficial as compared to using an index.

A full table scan might be performed, in case; there is no WHERE clause in a query for filtering out the rows which are shown in the result set.

Full Table Scan Using An Index

There are a few scenarios in which a complete table scan will take place even in the presence of an index on that table, such as:

A full table scan will be performed when a query does have a WHERE clause, however, not a single column in that WHERE clause is identical to the index’s leading column on the table.

A full table scan can still occur even if a query comprises of a WHERE clause having a column that is identical to the index’s first column. Generally, this situation arises when the use of an index is prevented because comparison is being used by the WHERE clause. Following are some scenarios in which that could happen:

  • When the wildcard operator is utilized in the comparison string’s first position.
  • When the NOT operator is used.
  • When the “<>“(NOT EQUAL) operator is used.

Leave a Reply