In databases, what is a full table scan? Also, what are some of the causes of full table scans?
A full table scan looks through all of the rows in a table – one by one – to find the data that a query is looking for. Obviously, this can cause very slow SQL queries if you have a table with a lot of rows – just imagine how performance-intensive a full table scan would be on a table with millions of rows. Using an index can help prevent full table scans.
Let’s go through some different scenarios which cause a full table scan:
Full table scan if statistics haven’t been updated
Normally, statistics are kept on tables and indexes. But, if for some reason table or index statistics have not been updated, then this may result in a full table scan. This is because most RDBMS’s have query optimizers that use those statistics to figure out if using an index is worthwhile. And if those statistics are not available, then the RDBMS may wrongly determine that doing a full table scan is more efficient than using an index.
If a query does not have a WHERE clause to filter out the rows which appear in the result set, then a full table scan might be performed.
Full table scan with an index
There are some scenarios in which a full table scan will still be performed even though an index is present on that table. Let’s go through some of those scenarios.
If a query does have a WHERE clause, but none of the columns in that WHERE clause match the leading column of an index on the table, then a full table scan will be performed.
Even if a query does have a WHERE clause with a column that matches the first column of an index, a full table scan can still occur. This situation arises when the comparison being used by the WHERE clause prevents the use of an index. Here are some scenarios in which that could happen:
- If the NOT EQUAL (the “<>“) operator is used. An example is “WHERE NAME <> ‘PROGRAMMERINTERVIEW'”. This could still result in a full table scan, because indexes are usually used to find what is inside a table, but indexes (in general) cannot be used to find what is not inside a table.
- If the NOT operator is used. An example is “WHERE NOT NAME = ‘PROGRAMMERINTERVIEW'”.
- If the wildcard operator is used in the first position of a comparison string. An example is “WHERE NAME LIKE ‘%INTERVIEW%'”.