How does an optimizer handle a query that selects from a view or derived table, and which view definitions stop it from merging that view into the outer query?

seniorshould knowfreq 45%

answer

view = macro, expanded then merged
merged -> one block -> predicates reach base tables
fences: GROUP BY, DISTINCT, window, LIMIT, UNION
symptom: view subtree rows >> result rows
CTE inlined vs materialized varies by engine

basics

~20 s

A view is expanded into its definition and, when possible, merged (inlined) into the outer query so predicates and joins can be reordered across the boundary. Constructs that make merging unsound - DISTINCT, GROUP BY or aggregates, window functions, row limits, set operations, volatile functions - turn the view into a fence that is materialized or evaluated separately first.

solid answer

~1 min

A view is not a stored result; the parser replaces the reference with the view's query text, giving a nested query block. The optimizer then attempts view merging (inlining): collapse the inner block into the outer one so there is a single flat block. That matters because optimization happens per query block - once merged, outer predicates can be pushed into the view's base tables, its joins can be reordered with outer joins, and indexes on base tables become reachable.

Merging is blocked when the inner block computes something whose value would change if you filtered or joined differently. Typical blockers: GROUP BY/aggregates, DISTINCT, window functions, LIMIT/FETCH/TOP, UNION/INTERSECT/EXCEPT, some outer-join positions, and volatile functions. Those make the block an optimization fence: it is optimized and executed on its own, its result feeds the outer query, and outer predicates are applied afterwards.

When merging fails, engines may still apply weaker transforms - pushing a predicate on a grouping column past a GROUP BY, or pushing a filter into each UNION ALL branch. The practical symptom of a fence is a view scanning far more rows than the outer query keeps; the fix is usually to remove the unnecessary DISTINCT or LIMIT, or to parameterize the view as a function or an inline predicate.

A view is not a stored result; the parser replaces the reference with the view's query text, giving a nested query block. The optimizer then attempts **view merging** (inlining): collapse the inner block into the outer one so there is a single flat block. That matters because optimization happens per query block - once merged, outer predicates can be pushed into the view's base tables, its joins can be reordered with outer joins, and indexes on base tables become reachable. Merging is blocked when the inner block computes something whose value would change if you filtered or joined differently. Typical blockers: `GROUP BY`/aggregates, `DISTINCT`, window functions, `LIMIT`/`FETCH`/`TOP`, `UNION`/`INTERSECT`/`EXCEPT`, some outer-join positions, and volatile functions. Those make the block an **optimization fence**: it is optimized and executed on its own, its result feeds the outer query, and outer predicates are applied afterwards. When merging fails, engines may still apply weaker transforms - pushing a predicate on a grouping column past a `GROUP BY`, or pushing a filter into each `UNION ALL` branch. The practical symptom of a fence is a view scanning far more rows than the outer query keeps; the fix is usually to remove the unnecessary `DISTINCT` or `LIMIT`, or to parameterize the view as a function or an inline predicate.

code

sql · 10 lines

sql

-- mergeable: outer filter reaches the base tables
CREATE VIEW v_open_orders AS
SELECT o.id, o.customer_id, o.total FROM orders o WHERE o.status = 'OPEN';

-- fenced: DISTINCT and LIMIT block merging
CREATE VIEW v_recent AS
SELECT DISTINCT o.customer_id, o.total
FROM orders o ORDER BY o.created_at DESC FETCH FIRST 1000 ROWS ONLY;

SELECT * FROM v_recent WHERE customer_id = 42;  -- filter applied after the 1000 rows

go deeper

for a junior

Say a view is expanded into its definition, and that some views can be inlined into the outer query while others cannot.

for a middle

List the merge blockers and explain why an outer filter then applies only after the view is computed.

for a senior

Diagnose from row counts in the plan subtree, distinguish full merging from restricted pushdown past aggregation, and prescribe the fix (drop the fence, parameterize, restructure layers).

for a principal

Set view-layering standards: merge-friendly base layers, aggregation only at the top, deliberate materialization where reuse justifies it, and engine-specific CTE semantics documented.

View expansion

A (non-materialized) view is a named query. When a statement references it, the analyzer substitutes the view's definition, producing a nested query block - exactly as if you had written a derived table in FROM. Nothing is stored, nothing is pre-computed; a view is a macro, not a cache.

Why merging matters

Optimizers reason mostly within a query block: join order, access paths and predicate placement are chosen per block. A nested block that stays nested is a wall - the outer query cannot reorder its joins with the inner ones, cannot push its predicates onto the inner base tables, and must consume whatever the inner block produces.

View merging (also called view inlining or subquery unnesting for FROM-clause blocks) collapses the inner block into the outer, producing one flat block over all base tables. Then the full search space opens: predicates from the outer WHERE land on inner base-table scans, join order is chosen globally, and index and partition access paths on the base tables become available.

When merging is unsound

Merging is legal only if flattening cannot change the inner block's computed values or row multiplicity:

Aggregation (GROUP BY, aggregate functions). Pushing an outer predicate inside would change which rows enter each group, and therefore the aggregate values. Only predicates on grouping columns can be pushed - a limited transform sometimes called group-by pushdown or predicate pushdown past aggregation.
DISTINCT. De-duplication is over the view's own projection; joining first and de-duplicating later is a different query.
Window functions. A window frame depends on the exact input row set and its ordering; removing rows earlier changes every computed value. This makes windowed views hard fences.
LIMIT/FETCH FIRST/TOP. The view says 'these N rows'. Filtering before the limit picks a different N.
Set operations. UNION implies de-duplication across branches. UNION ALL is friendlier: predicates can usually be pushed into each branch independently.
Volatile or side-effecting functions. The number of evaluations is observable.
Outer-join positions. Merging a block that sits on the null-supplying side can change NULL-extension.

Engines also add heuristics: even where merging is legal, a very large flattened block may be split to keep planning time bounded, and some engines expose explicit fence hints or materialization directives.

Symptoms and diagnosis

The classic symptom: a query filtering on one customer runs for minutes because it selects from a reporting view that aggregates or de-duplicates the whole table first; the plan shows the view's subtree processing millions of rows while the final output is a handful. Read the plan bottom-up and compare the row count inside the view's subtree with the outer result - a large gap means the outer predicate did not reach the base tables.

Remedies

Remove gratuitous fences. A DISTINCT added defensively, or an ORDER BY/LIMIT inside a view, is often unnecessary and is the entire cost.
Parameterize. A table-valued function or an inline derived table carrying the filter inside makes the predicate reach the base tables by construction.
Split the layers. Filter the base tables in a CTE or derived table first, then aggregate - move the aggregation above the filter rather than hoping the rewriter can.
Materialize deliberately. If the fenced computation is expensive and reused, a materialized view or a summary table turns a per-query cost into a maintained one.
Know your engine's CTE semantics. In some engines a common table expression is itself an optimization fence (materialized); in others it is inlined like a view. That difference alone can be a 100x plan change, so verify rather than assume.

Layering guidance

Views stacked several deep multiply the risk: each layer that adds a DISTINCT or an aggregate is another fence, and the outer predicate may end up applied only at the very top. When a view hierarchy is a product requirement, keep the lower layers merge-friendly - plain projections and joins, no de-duplication, no limits - and put aggregation only in the topmost layer that needs it.

## View expansion A (non-materialized) view is a named query. When a statement references it, the analyzer substitutes the view's definition, producing a nested query block - exactly as if you had written a derived table in `FROM`. Nothing is stored, nothing is pre-computed; a view is a macro, not a cache. ## Why merging matters Optimizers reason mostly *within* a query block: join order, access paths and predicate placement are chosen per block. A nested block that stays nested is a wall - the outer query cannot reorder its joins with the inner ones, cannot push its predicates onto the inner base tables, and must consume whatever the inner block produces. **View merging** (also called view inlining or subquery unnesting for `FROM`-clause blocks) collapses the inner block into the outer, producing one flat block over all base tables. Then the full search space opens: predicates from the outer `WHERE` land on inner base-table scans, join order is chosen globally, and index and partition access paths on the base tables become available. ## When merging is unsound Merging is legal only if flattening cannot change the inner block's computed values or row multiplicity: - **Aggregation (`GROUP BY`, aggregate functions).** Pushing an outer predicate inside would change which rows enter each group, and therefore the aggregate values. Only predicates on grouping columns can be pushed - a limited transform sometimes called group-by pushdown or predicate pushdown past aggregation. - **`DISTINCT`.** De-duplication is over the view's own projection; joining first and de-duplicating later is a different query. - **Window functions.** A window frame depends on the exact input row set and its ordering; removing rows earlier changes every computed value. This makes windowed views hard fences. - **`LIMIT`/`FETCH FIRST`/`TOP`.** The view says 'these N rows'. Filtering before the limit picks a different N. - **Set operations.** `UNION` implies de-duplication across branches. `UNION ALL` is friendlier: predicates can usually be pushed into each branch independently. - **Volatile or side-effecting functions.** The number of evaluations is observable. - **Outer-join positions.** Merging a block that sits on the null-supplying side can change NULL-extension. Engines also add heuristics: even where merging is legal, a very large flattened block may be split to keep planning time bounded, and some engines expose explicit fence hints or materialization directives. ## Symptoms and diagnosis The classic symptom: a query filtering on one customer runs for minutes because it selects from a reporting view that aggregates or de-duplicates the whole table first; the plan shows the view's subtree processing millions of rows while the final output is a handful. Read the plan bottom-up and compare the row count *inside* the view's subtree with the outer result - a large gap means the outer predicate did not reach the base tables. ## Remedies 1. **Remove gratuitous fences.** A `DISTINCT` added defensively, or an `ORDER BY`/`LIMIT` inside a view, is often unnecessary and is the entire cost. 2. **Parameterize.** A table-valued function or an inline derived table carrying the filter inside makes the predicate reach the base tables by construction. 3. **Split the layers.** Filter the base tables in a CTE or derived table first, then aggregate - move the aggregation above the filter rather than hoping the rewriter can. 4. **Materialize deliberately.** If the fenced computation is expensive and reused, a materialized view or a summary table turns a per-query cost into a maintained one. 5. **Know your engine's CTE semantics.** In some engines a common table expression is itself an optimization fence (materialized); in others it is inlined like a view. That difference alone can be a 100x plan change, so verify rather than assume. ## Layering guidance Views stacked several deep multiply the risk: each layer that adds a `DISTINCT` or an aggregate is another fence, and the outer predicate may end up applied only at the very top. When a view hierarchy is a product requirement, keep the lower layers merge-friendly - plain projections and joins, no de-duplication, no limits - and put aggregation only in the topmost layer that needs it.

A view that aggregates blocks merging - can any predicate still be pushed into it?
Yes: a predicate that references only grouping columns can be pushed below the aggregation, because it removes whole groups rather than changing the contents of surviving ones. A predicate over an aggregate result cannot move. Many optimizers implement exactly this restricted transform, which is why filtering a summary view by its group key is usually fast while filtering by a computed total is not.
How does a common table expression differ from a view here?
Syntactically a CTE is a named inline block, but its optimization treatment varies: some engines always materialize CTEs, making them hard fences; others inline them like views unless the CTE is recursive or referenced multiple times, and some offer explicit materialized/not-materialized control. Because the difference changes whether outer predicates reach base tables, you must check the engine and version rather than assume.

saying these in an interview costs you the question

Believing a plain view stores or caches rows
Assuming an outer WHERE always reaches the view's base tables
Adding DISTINCT to views defensively without noticing it creates a fence
Treating CTEs as always inlined (or always materialized) across all engines
Blaming statistics when the real issue is a non-mergeable block

How does an optimizer handle a query that selects from a view or derived table, and which view definitions stop it from merging that view into the outer query?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

View expansion

Why merging matters

When merging is unsound

Symptoms and diagnosis

Remedies

Layering guidance

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions8 questions

▾related topics3 topics

View expansion

Why merging matters

When merging is unsound

Symptoms and diagnosis

Remedies

Layering guidance

answer

go deeper

context