Why is searching for large-table full-table scans critical to SQL tuning?
-
They indicate an optimized execution plan.
-
They may be able to be tuned to use an index
-
The full-table scan should be normalized from the database design
-
A full-table scan is always sub-optimal.
To answer this question, the user needs to understand the basics of SQL tuning and database optimization.
The correct answer is:
B. They may be able to be tuned to use an index
Explanation:
A full-table scan is a process in which the database engine reads all the rows of a table to find the relevant data. This can be a time-consuming process, especially for large tables. Full-table scans are not always sub-optimal, but they can be inefficient if the table is not properly indexed. When a table is not indexed correctly, the database engine must read every row of the table to find the relevant data, even if only a small subset of the data is needed.
Therefore, searching for full-table scans is critical to SQL tuning because it allows a database administrator to identify tables that are not properly indexed. Once identified, the administrator can then tune the full-table scan to use an index, which can improve performance and reduce the time it takes to retrieve the data.
Option A is incorrect because a full-table scan does not indicate an optimized execution plan. It may be the only option available, but it is not necessarily optimized.
Option C is incorrect because a full-table scan is not something that should be normalized from the database design. It is a method of retrieving data from a table and can be optimized through proper indexing.
Option D is incorrect because a full-table scan is not always sub-optimal. It depends on the size of the table and how well it is indexed.
A full-table scan on a large table is a red flag for SQL tuning because it often means the optimizer isn't finding a more selective path, such as an index, to reach the needed rows — so the query touches far more data than necessary. It isn't automatically 'optimized' or automatically 'always sub-optimal' (sometimes a full scan is genuinely the best plan for a small or fully-needed result set), which is why the right reasoning is that it flags a scan that may be tunable with an index.