Get the Preplance app for a seamless learning experience. Practice offline, get daily streaks, and stay ahead with real-time interview updates.
Get it on
Google Play
4.9/5 Rating on Store
SQL & Databases · Question Set
Query Optimization & Execution Plans interview questions for placements and exams.
Questions
14
Included in this set
Subject
SQL & Databases
Explore more sets
Difficulty
Mixed
Level of this set
Go through each question and its explanation. Use this set as a focused practice pack for SQL & Databases.
Filtering the raw column with a range allows an index seek. Wrapping the column in a function blocks index use in many engines and forces scans. A half-open range (greater-than-or-equal to start and less than next day) is even safer across time types and precision.
WHERE created_at >= '2025-10-01' AND created_at < '2025-10-02'
For complete preparation, combine this set with full subject-wise practice for SQL & Databases. You can also explore other subjects and sets from the links below.
If an index provides the required order, the engine can scan it and return rows already sorted. This removes the need for a costly sort step. Column order and ASC or DESC matter. Consider covering includes to avoid extra lookups.
CREATE INDEX idx_orders_date_id ON orders(order_date DESC, id DESC);
Segment queries so highly skewed predicates use a dedicated path. For example, branch on a flag to call a version optimized for rare values. Use parameterization with sensible defaults, and consider OPTIMIZE FOR or recompile hints cautiously when estimates are volatile. Add supporting indexes that match the common filter patterns, and ensure statistics contain histograms on skewed columns. Monitor with plan hash and track regressions when distributions shift.
-- Example branching IF @IsRare=1 SELECT ... WHERE country='VA' OPTION (RECOMPILE); ELSE SELECT ... WHERE country=@country;
EXPLAIN shows how the optimizer intends to execute your SQL: join order, access paths, estimated rows, and operator costs. With ANALYZE, you also see actual rows and timing. You use this to spot scans where seeks are expected, poor join choices, and misestimates that hint at stale statistics or non-SARGable predicates.
EXPLAIN ANALYZE SELECT o.id, c.name FROM orders o JOIN customers c ON c.id=o.customer_id WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days';
Merge join streams through two sorted inputs and matches rows in order. When both sides are sorted (or cheap to sort) it can be very fast and memory-friendly. Hash join excels with large, unsorted sets and equality predicates. Nested loops shine when the outer is small and the inner has an efficient seek.
/* Engine chooses merge when indexes support order: JOIN ON t1.k = t2.k with both indexed by k */
The cost model relies on estimated row counts. If estimates are wrong, the optimizer may choose scans, wrong join algorithms, or expensive rehashes. Refreshing statistics, using SARGable predicates, and avoiding functions on columns help the engine estimate more accurately.
-- PostgreSQL ANALYZE sales; -- SQL Server UPDATE STATISTICS sales;
Wide ORs often disable index seeks. Splitting into separate queries lets each branch use the best index, then UNION ALL combines results. Add DISTINCT only if needed, since it adds sort or hash work. Ensure predicates are mutually exclusive or dedupe explicitly.
(SELECT * FROM users WHERE email = $1) UNION ALL (SELECT * FROM users WHERE phone = $2)
On first execution, the engine compiles a plan using the sniffed parameter. If that value is atypical, the cached plan can be bad for other values. Mitigations include recompiling, using option hints, parameterizing carefully, or separating hot and cold paths with targeted queries.
-- SQL Server example EXEC sp_recompile N'dbo.SearchOrders';
With input sorted by group keys, a streaming aggregate walks once and emits groups as they change. It avoids building a hash table and reduces memory. Hash aggregate is great for large, unsorted sets but may spill if memory is tight.
SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id; -- index on (customer_id) enables stream aggregate
A materialized view trades storage and refresh work for faster reads, which helps heavy, repeated analytics. A plain view is only a saved query definition. Choose refresh timing based on freshness needs. Some engines support fast refresh with logs; others only full refresh.
-- Postgres
CREATE MATERIALIZED VIEW mv_sales AS
SELECT date_trunc('day', created_at) d, SUM(total) s
FROM orders GROUP BY 1;Filter pushdown means applying selective predicates as close to the base tables as possible. This prevents unnecessary row explosion in joins and reduces the amount of data flowing to sorts, aggregates, and window operations. Smaller inputs improve cache locality, cut memory use, and allow different join algorithms. The effect is multiplicative across multi-join plans, so early reduction often yields the biggest gains.
SELECT ... FROM orders o JOIN items i ON i.order_id=o.id WHERE o.status='PAID' AND o.created_at>=CURRENT_DATE-INTERVAL '30 days';
Use a temp table when you need to reuse an expensive intermediate result multiple times, add indexes to it, or break a huge plan into steps to help the optimizer. It can reduce re-computation and gives the engine cardinality facts after indexes are built. A CTE is great for readability in a single statement, but many engines inline it. If the logic is heavy and reused, a temp table with the right index can be faster and more predictable.
CREATE TEMP TABLE t AS SELECT * FROM big WHERE flag=1; CREATE INDEX ON t(user_id); SELECT ... FROM t JOIN users u ON u.id=t.user_id;
Hints can force join orders or algorithms, but they lock the plan to today’s data shape. Over time, they may hide underlying issues like missing indexes or bad statistics and can age poorly. Use hints as a last resort for stable, critical queries after fixing schema and statistics. Document why the hint exists and review it when data distributions change.
-- Example (syntax varies) SELECT /*+ USE_HASH(o c) LEADING(o c) */ ...
First make the ORDER BY for the window align with an index to enable ordered scans and reduce sorting. Partition by selective keys to keep frames small. Prefer ROWS frames for predictable work and add a tiebreaker to the order to avoid large peer groups. If you only need aggregates at coarser grains, pre-aggregate in a CTE or temp table before the window step. For time series, consider partitioning or pruning to restrict the scanned time range.
CREATE INDEX idx_sales_pid_day ON sales(product_id, day);
SELECT product_id, day,
SUM(amount) OVER (PARTITION BY product_id ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rt
FROM sales WHERE day >= CURRENT_DATE - INTERVAL '180 days';