Skip to content

DA7. Materialized Views

Statement

Define and discuss the advantages and disadvantages of materialized views in databases.

Answer

  • A Materialized View is a database object that stores the result of a query and can be queried as a table; it is a pre-computed query result that saves the hassle of re-computing the query result every time it is queried (Archer, 2022).
  • The main use cases for Materialized Views includes caching the results of complex queries (usually queries that hold a lot of joins), and creating a partial read-only copy of a table for a subset of users (Microsoft, 2022).
  • The main scenarios to avoid using Materialized Views include increasing the storage space, and increasing overhead when updating the underlying table (especially, if the DBMS does not support the automatic refresh of the Materialized View) (Archer, 2022).

Advantages of Materialized Views

  • Materialized Views cache the results of complex queries, which can be used to speed up the response time, especially if the query is executed frequently and contains a lot of heavy joins or aggregations.
  • Since Materialized Views are pre-computed, they can be used to reduce the load on the database, where the DBMS will do the joins/aggregations less often.
  • Materialized Views can serve as a way to control access to the data in the underlying table, by creating a partial read-only copy of the table for a subset of users that only holds parts of the table that are relevant to them.

Disadvantages of Materialized Views

  • Using Materialized Views where they have no use case (e.g. there is no difference between the view and the underlying table) will increase the storage space consumed on the DBMS.
  • Even if the usage of Materialized Views is justified, the added storage space may be considered and must be weighed against the benefits of using Materialized Views.
  • Not all DBMSs support the automatic refresh of Materialized Views, which means that the Materialized View must be manually refreshed whenever the underlying table is updated. This can be a significant overhead if the Materialized View is updated frequently. The manual refresh of Materialized Views can be automated using procedures, triggers, or server cron jobs.

Conclusion

  • Materialized Views are a useful tool for caching, controlling access to data, and reducing the load on the database.
  • Materialized Views should be used with caution, as they can increase the storage space and overhead when updating the underlying table.
  • The heavy use of Materialized Views may be a sign indicating that you should consider de-normalizing the database schema (Wright, n.d.)

References