In relational databases, what is the difference between a view and a materialized view in terms of storage and refresh behaviour?

Introduction / Context:
Views and materialized views are both logical objects that present data from underlying tables, but they behave very differently in terms of storage, performance, and freshness. This distinction is important for designing reporting solutions and optimizing query performance, so it is a common topic in database and data warehousing interviews.

Given Data / Assumptions:

• The database supports both standard views and materialized views.
• Underlying base tables can be updated independently of the view definitions.
• Users may require either real-time data or precomputed, aggregated data.
• We are concerned with how data is stored and refreshed for each type.

Concept / Approach:
A regular view is essentially a saved query. It does not store data itself; when you select from the view, the database re executes the underlying query against the base tables. This means the view always reflects the current state of those tables but may be expensive to query if the logic is complex. A materialized view, in contrast, stores the result set on disk like a table. Queries against a materialized view can be much faster, especially for aggregates, but the data can become stale and needs explicit or scheduled refreshes.

Step-by-Step Solution:
Step 1: Define a view as a virtual table that stores a SELECT statement, not its result. Step 2: Explain that when users query the view, the database dynamically executes the SELECT and returns up to date data from the base tables. Step 3: Define a materialized view as an object that stores the query's result set physically, often in a separate segment or table structure. Step 4: Note that materialized views improve performance because queries can read precomputed data instead of recomputing joins and aggregations each time. Step 5: Emphasize that materialized views require refresh strategies (on demand, on commit, or scheduled) to keep their data synchronized with the base tables.

Verification / Alternative check:
Examining database metadata reveals that views have no separate storage space apart from their definition, while materialized views consume storage similar to tables. Running EXPLAIN plans shows that queries on views generally expand into the underlying query, whereas queries on materialized views often access a single prebuilt structure. Monitoring refresh operations confirms that materialized views do not update automatically unless configured to do so, which demonstrates the trade off between speed and freshness.

Why Other Options Are Wrong:
Option B incorrectly claims that a view is always faster and that materialized views are only useful for inserts, which is not accurate; materialized views are specifically designed to speed up complex reads. Option C artificially limits the number of base tables, which has nothing to do with the difference between views and materialized views. Option D ignores the clear implementation and behavioural differences documented in most relational database systems.

Common Pitfalls:
A common mistake is assuming that a materialized view always shows real-time data; in reality, it can lag significantly behind the base tables if refreshes are infrequent. Another pitfall is overusing materialized views without considering the overhead of storage, maintenance, and refresh operations. Good design identifies critical, expensive queries that justify materialization while leaving simpler reports to use normal views or direct table access.

Final Answer:
A view is a virtual table that stores only the query and always reflects current base table data, while a materialized view is a physically stored result set that improves performance but must be refreshed to stay in sync with the source data.