Answer

In SQL, there are two types of views:

• Regular views (View): logical virtual tables. They do not store data; a query to a VIEW generates a subquery to the underlying tables each time.
• Materialized views (Materialized View): physically store the result of the query in a separate table, which is periodically updated.

Regular Views are convenient for abstracting complexity, simplifying access, and combining data from multiple sources. They do not speed up queries, as they are always generated on-the-fly.

Materialized Views provide performance benefits for complex reports and analytics, where it is important not to wait for aggregations and joins each time. They need to be updated manually or on a schedule to prevent data from becoming stale.

Example of a regular VIEW:

CREATE VIEW active_users AS
SELECT id, name FROM users WHERE status = 'active';

Example of a materialized VIEW (PostgreSQL):

CREATE MATERIALIZED VIEW active_users_agg AS
SELECT country, COUNT(*) as cnt FROM users WHERE status = 'active' GROUP BY country;
-- To update:
REFRESH MATERIALIZED VIEW active_users_agg;

Trick Question

Can data be updated in VIEW, and how does this depend on the type of VIEW?

It is often mistakenly believed that VIEWs are fully identical to tables in terms of updatability. IN REALITY:

• Regular VIEWs rarely allow updates: only if there are no aggregate, group, or composite/computed fields (and even then, lacking JOINs and subqueries).
• Materialized VIEWs cannot be updated directly at all — only through REFRESH, otherwise there will be data inconsistency.

Examples of real mistakes due to ignorance of the nuances of the topic

---

Story 1

A BI report was built through a regular VIEW with several JOINs and aggregates. After an increase in load, the report generation time grew to tens of minutes. A systems analyst suggested a materialized view, which instantly reduced the time to seconds, as data began to be stored in a separate table.

---

Story 2

A developer attempted to perform an UPDATE through a regular VIEW during migration to Oracle, causing an error: "view with group by is not updatable". The reason was the use of GROUP BY in the view.

---

Story 3

In one company, they forgot to update the materialized view after importing new data, leading to report inconsistencies among different users, as the analytics worked with outdated data from this VIEW. They later added an automatic REFRESH on a schedule.