SQL and Query Processing
This domain tests SQL semantics and how engines turn a statement into an execution plan. NDB does not run SQL itself; it manages engines, so you must recognize optimizer behavior across Oracle, SQL Server, PostgreSQL, and MySQL.
Processing pipeline
- Parse and validate syntax and privileges.
- Rewrite and transform the query.
- Optimize using statistics and cost models; choose access paths and join methods.
- Execute and fetch rows.
- Access paths include full table scan, index range scan, index-only scan, and bitmap index scan. Join methods include nested loops, hash join, and merge join.
Worked example
Query: SELECT order_id, total FROM orders WHERE customer_id = 42 AND order_date >= DATE '2025-01-01'. With a composite index on (customer_id, order_date), the optimizer can perform an index range scan: equality on the leading column, range on the trailing column. If the query also joins a large customers table, a hash join is likely for a large scan, while nested loops suit a highly selective outer row set. Reasoning: the index avoids scanning 50 million rows, and the join method follows from cardinality estimates. If statistics are stale, the estimate may be wrong and the optimizer may choose a full scan.
Traps
- Applying a function to an indexed column, which disables a normal index range scan.
- Implicit data-type conversion causing unexpected casts.
- Stale or missing statistics after a large load.
- Assuming a hint overrides a fundamentally bad schema or missing index.
- Ignoring bind-variable peeking and plan instability.