Can you explain the concept of Materialized View and how it differs from regular views in a database?

Materialized View is a database object that stores the result of a query as a physical table. Unlike a regular view, which is virtual and computed each time it is accessed, a materialized view stores data and is refreshed on a schedule or manually.

Main differences:

• Regular View:

  • Does not store data physically.
  • Query is executed each time it is accessed.
  • Always up-to-date, but can be slow with complex queries.

• Materialized View:

  • Stores data on disk.
  • Faster to read, as data is precomputed.
  • Requires refresh to update data.

Example usage in SQL:

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

-- Refresh the materialized view
REFRESH MATERIALIZED VIEW sales_summary;

Thus, a materialized view is useful for speeding up queries where data does not change very often.