CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is investigating slow query performance for a critical report. The report query involves joining several large tables and filtering on a date range. The administrator observes that the query execution plan consistently shows a 'Table Scan' operation on one of the largest tables, even though an index exists on the date column used in the WHERE clause. What is the MOST likely reason for the optimizer to choose a table scan over an index seek in this scenario?
- AThe query optimizer determined that a large percentage of rows would be returned by the filter.
- BThe statistics on the table or index are outdated.
- CThe index is fragmented, making it inefficient to use.
- DThe index is not covering all columns required by the query.
Show answer & explanationAnswer & explanation
Correct answer: A. The query optimizer determined that a large percentage of rows would be returned by the filter.
Query optimizers use statistics to estimate the selectivity of an index. If the optimizer estimates that a very large percentage of the rows in the table will be returned by the filter (e.g., more than 20-30%), it may determine that a full table scan is more efficient than reading a large portion of the index and then performing many random lookups into the table.
Why the other options are wrong
- B. Outdated statistics can lead to poor plan choices, but the specific choice of a table scan over an index seek for a large percentage of rows is a direct consequence of the optimizer's cost model, which relies on statistics.
- C. Index fragmentation can degrade performance but typically wouldn't cause the optimizer to completely ignore an index in favor of a table scan unless the fragmentation is extreme and the table is small.
- D. A non-covering index would require additional lookups to the base table, but if the filter is highly selective, the optimizer would still likely use the index for filtering and then perform key lookups, rather than a full table scan.
Index Selectivity
A measure of how many rows an index lookup is expected to return for a given search condition. High selectivity means fewer rows are returned.
- Query optimizers use index selectivity to estimate the cost of using an index.
- Indexes are most effective for highly selective queries (returning a small percentage of rows).
- If an index is not selective enough, the optimizer may choose a table scan instead.
Memory trick: Optimizer weighs costs, not just existence.