What is a materialized view?

Learn the view that stores its result.

What is a materialized view?

A materialized view is a view that stores its result. It physically holds data when created.

CREATE MATERIALIZED VIEW sales_summary AS
SELECT category, SUM(amount) AS total
FROM sales GROUP BY category;

This query stores the summary result.

The materialized view sales_summary is created. category, SUM(amount) AS total is selected from sales. Rows are grouped by category; aggregate functions are computed per group.

What is a materialized view? — details

A materialized view does not recompute on every query. It stores the result, refreshed with REFRESH.

It is ideal for dashboard reports that only need hourly or daily updates: the heavy aggregation runs once and every reader gets the stored result instantly.

Exercise What is a materialized view?

Create the sales_summary materialized view.

Show example solution

CREATE MATERIALIZED VIEW sales_summary AS
SELECT category, SUM(amount) AS total
FROM sales GROUP BY category;

Test yourself

What does a materialized view store?
- The query
- The result
- The index
- The trigger
Show answer

The result — A materialized view stores the result.

Add MATERIALIZED

CREATE ____ VIEW sales_summary AS
SELECT category, SUM(amount) FROM sales GROUP BY category;

Show answer

CREATE MATERIALIZED VIEW sales_summary AS
SELECT category, SUM(amount) FROM sales GROUP BY category;

What is a materialized view: match the terms

materialized

physical
result

store
REFRESH

update
view

virtual

Next lesson: CREATE

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies