What is a materialized view and how does it relate to denormalization?

• A. A materialized view is a virtual table that stores no data.
• B. A materialized view is updated only when explicitly refreshed.
• C. A materialized view stores the results of a query virtually, not in physical storage.
• D. A materialized view stores the results of a query physically, offering performance benefits similar to denormalization but managed separately.

Answer: (D)

Materialized views store the results of a query physically as a separate object in the database. This means the data is materialized—taken from the underlying tables, joined or aggregated as needed, and saved on disk so future queries can read the precomputed result directly rather than recomputing it each time. This gives a big read-performance boost for complex queries, especially those involving joins, aggregations, or large datasets.

Their relation to denormalization is practical: both aim to speed up read queries by presenting data in a way that’s easier to access. A materialized view achieves this without permanently altering the base schema. It creates a precomputed snapshot that resembles a denormalized result set, but it lives as its own object and must be refreshed to stay up to date with the normalized base tables. Refresh behavior can be manual, on commit, or on a schedule, depending on the database system and configuration.

The other statements miss essential aspects. One describes a virtual view that stores no data, which is not a materialized view. Another claims the data is stored virtually rather than physically, which also describes a normal view. The remaining option correctly notes the physical storage and the performance benefits tied to that approach while highlighting its managed refresh nature.