what is a full table scan?
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
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
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.
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.
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%’”.
Read full article from Database Full Table Scan
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
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
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.
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.
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%’”.
Read full article from Database Full Table Scan
No comments:
Post a Comment