Query Processor

Definition

The Query Processor is the DBMS subsystem that transforms a declarative SQL statement into an efficient sequence of physical operations, so the same SQL can run correctly — and reasonably fast — whether a table has ten rows or ten million.

How It Works — The Four Stages

1. Parsing — checks the SQL's syntax and produces a parse tree representing the statement's structure.
2. Semantic validation / binding — checks that every referenced table and column actually exists (using catalog metadata owned by the Database Engine) and that data types are compatible.
3. Query optimization — the optimizer considers multiple logically equivalent execution plans for the same query (for example: index scan versus full table scan; nested-loop join versus hash join) and picks the one it estimates will be cheapest, using statistics about table sizes and data distribution.
4. Execution — the chosen plan actually runs, pulling data through the Buffer Manager and Storage Manager, and streaming rows back to the client.

Example: for SELECT * FROM students WHERE department = 'Computer Science' ORDER BY marks DESC, if there is an index on department, the optimizer may choose an index scan to retrieve only the matching rows instead of a full table scan, and then sort the resulting smaller set by marks.

Edge Cases and Pitfalls

• Stale statistics lead to bad plans. The optimizer's cost estimates depend on statistics about table size and data distribution. After large bulk inserts or deletes, these statistics can become stale, causing the optimizer to choose a poor plan (e.g., a full table scan when an index scan would now be far cheaper) until statistics are refreshed (e.g., ANALYZE).
• *`SELECT ` can block certain optimizations.* Requesting all columns prevents optimizations like an index-only scan* (which can be used only when every needed column is present directly in the index) that a narrower, explicit column list might have allowed.
• Non-sargable predicates defeat indexes. Wrapping a column in a function inside a WHERE clause — for example, WHERE UPPER(department) = 'CS' — can prevent the optimizer from using a plain index on department, since the indexed values and the function's output no longer match directly (unless a matching functional index exists).

Key Takeaways / Interview Q&A

Q: What are the four stages the Query Processor takes a SQL statement through? A: Parsing, semantic validation/binding, query optimization, and execution.

Q: Why might the optimizer choose a full table scan even when an index exists on the filtered column? A: Because the optimizer's cost-based decision depends on statistics — if the index isn't selective enough for the current data distribution (or the statistics are stale), a full scan can genuinely be estimated as cheaper.

Q: Why can `WHERE UPPER(department) = 'CS'` be slower than `WHERE department = 'CS'` even with an index on `department`? A: Because wrapping the column in a function makes the predicate non-sargable — the optimizer generally cannot use a plain index on department to satisfy a condition on UPPER(department).