View Limitations

Views Are Convenient, Not Magic

Views solve real problems — abstraction, security, reuse — but they come with structural limitations that every SQL developer needs to know before relying on them too heavily.

1. Ordinary Views Cannot Be Indexed Directly

Because a plain view has no physical storage, you cannot create an index on the view itself:

sql
CREATE VIEW high_earners AS
SELECT id, name, salary FROM employees WHERE salary > 80000;

CREATE INDEX idx_he_salary ON high_earners (salary);  -- ERROR in most engines

Any indexing must happen on the base table (CREATE INDEX ON employees (salary)), which the view's query can then take advantage of when the optimizer inlines the view definition. The one major exception: materialized views can be indexed directly (19.6), since they are physically stored, real objects — this is one of the biggest reasons to materialize an expensive view in the first place.

2. Nested Views Are Hard to Reason About

A view can be built on top of another view:

sql
CREATE VIEW engineering_high_earners AS
SELECT * FROM high_earners WHERE department = 'Engineering';

This works, but as nesting grows deeper (view_c on view_b on view_a on a table), two problems compound:

• Debugging performance becomes difficult — EXPLAIN output on the outermost view can turn into a large, hard-to-read plan spanning several inlined query fragments, making it unclear which layer is responsible for a slow scan or a missing index opportunity.
• Reasoning about correctness becomes difficult — understanding what view_c actually returns requires mentally unwinding three layers of WHERE/JOIN/GROUP BY logic, and a change to any middle layer (view_b) can have non-obvious effects on view_c's results.

As a practical guideline, teams typically cap view nesting depth (e.g., no more than one or two layers) and prefer flattening a chain of views back into a single well-documented view when it grows unwieldy.

3. Engine-Specific Restrictions on Updatable Views

Beyond the general rules in 19.5 (no joins, no aggregation, no GROUP BY/DISTINCT), specific engines impose their own additional constraints on what counts as an updatable view — for example, restrictions around:

• Views containing window functions.
• Views with certain subquery forms in the WHERE clause.
• Views combining a LIMIT/TOP clause with INSERT/UPDATE.

These vary enough between MySQL, PostgreSQL, and Oracle that "is this view updatable?" often needs to be verified against the specific engine's documentation (or its information_schema.views.is_updatable column, where offered) rather than assumed from general SQL knowledge.

4. Views Don't Improve Performance By Themselves

A common misconception: wrapping a slow query in a view makes it fast. It does not — a plain view is just a stored alias for the same query, with the same execution cost. (Only a materialized view changes the performance characteristics, by trading freshness for stored results — 19.6.)

5. Permissions Complexity Can Grow

As seen in 19.4 and 19.7, views used heavily for security create their own maintenance burden: many narrow, purpose-built views (staff_view, manager_view, hr_view, audit_view, ...) each need their own grants tracked and kept in sync as roles or requirements evolve — a form of security-configuration sprawl that needs its own governance.

Edge Cases

• Circular view references are impossible — a view cannot (directly or indirectly) reference itself, since it must exist before being referenced.
• A materialized view built on a nested chain of ordinary views still recomputes the entire chain on each REFRESH, so nesting doesn't disappear just because the outer object is materialized — only the read path becomes fast.
• `information_schema.views` and engine-specific catalogs are the authoritative way to check whether a specific view, in a specific engine, is currently classified as updatable, indexable, etc. — don't assume from general rules alone.

Key Takeaways / Interview Q&A

Q: Can you put an index directly on a regular (non-materialized) view? A: No — indexes go on the base table (or on a materialized view, since that one is physically stored).

Q: What's the main risk of deeply nested views? A: Difficulty reasoning about correctness and debugging performance, since understanding the outermost view requires unwinding every layer beneath it, and changes to a middle layer can have non-obvious downstream effects.

Q: Does wrapping a slow query in a plain view make it faster? A: No — a plain view has the same execution cost as its underlying query every time it's queried; only materializing it changes that.