SQL performance guideline: As a general rule, should queries that perform full table scans be avoided when selective access paths (e.g., indexes) are available?

Introduction / Context:Query performance often hinges on how data is accessed. Full table scans read every row, which can be costly. This question checks whether avoiding them is a sensible default practice.

Given Data / Assumptions:

• Large tables can contain millions of rows.
• Indexes provide selective access paths when predicates are selective.
• Optimizers choose plans based on statistics.

Concept / Approach:As a general guideline, avoid unnecessary full scans by providing appropriate indexes and predicates. However, full scans can be optimal for small tables, unselective predicates, or when most rows are needed. The statement emphasizes the “avoid when possible” principle, not an absolute rule.

Step-by-Step Solution:Profile workload and identify long-running queries.Check execution plans; note full table scans on large tables.Add or tune indexes that match selective predicates or join keys.Update statistics and verify plan improvements.

Verification / Alternative check:Benchmark the same query with and without a supporting index on representative data volumes; the indexed plan typically reduces I/O and elapsed time.

Why Other Options Are Wrong:Restricting the guidance to tiny tables or cloud databases misunderstands fundamentals. Marking “Incorrect” ignores the common performance trade-off.

Common Pitfalls:Creating too many indexes (hurting writes); stale statistics; predicates that prevent index usage (functions on columns, mismatched data types).

Final Answer:Correct