Last modified: May 11, 2025
This article is written in: πΊπΈ
Materialized views are a database feature that allows you to store the result of a query physically on disk, much like a regular table. Unlike standard views, which are virtual and execute the underlying query each time they are accessed, materialized views cache the query result and can be refreshed periodically. This approach significantly improves performance for complex queries, especially when dealing with large datasets or computationally intensive operations.
After reading the material, you should be able to answer the following questions:
Imagine you have a complex query that aggregates sales data across multiple regions and products. Running this query every time can be time-consuming and resource-intensive. A materialized view lets you store the result of this query, so subsequent accesses are faster because the database doesn't have to re-execute the computation each time.
Here's a simple representation of how materialized views fit into a database system:
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Phase 1: Materialized-View Refresh (periodic or on-demand)
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
+-----------------------+ [1] Complex aggregation
| Base Tables | βββββββββββββββββββββββββββββΊ
+-----------------------+
|
|
βΌ
+-----------------------+
| Materialized View |
+-----------------------+
(persisted snapshot of Base Tables)
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Phase 2: Query Phase (fast reads against the pre-computed view)
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
+-----------------------+ [2] Simple lookup/join
| Materialized View | βββββββββββββββββββββββββββββΊ
+-----------------------+
|
|
βΌ
+-----------------------+
| Query Result |
+-----------------------+
Materialized views offer several advantages:
Since data in the underlying tables can change, materialized views can become outdated. Refreshing a materialized view updates it with the latest data.
Let's walk through an example of creating and using a materialized view in PostgreSQL.
Suppose you have a table called sales
with millions of records, and you often run a query to get total sales per region.
CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;
This command creates a materialized view named sales_summary
that stores the total sales per region.
SELECT * FROM sales_summary;
When you run this query, PostgreSQL retrieves data directly from the sales_summary
materialized view, which is faster than executing the aggregation on the entire sales
table.
After new sales data is inserted into the sales
table, you can refresh the materialized view to include the latest data:
REFRESH MATERIALIZED VIEW sales_summary;
You can automate the refresh process using a scheduled task or a cron job. For example, to refresh the materialized view every night at midnight, you might set up a cron job with the following command:
0 0 * * * psql -U username -d database_name -c "REFRESH MATERIALIZED VIEW sales_summary;"
Just like regular tables, you can create indexes on materialized views to further enhance query performance.
CREATE INDEX idx_sales_summary_region ON sales_summary(region);
This index speeds up queries that filter or join on the region
column.
Materialized views are especially useful in scenarios where complex queries are frequently executed, and real-time data is not a strict requirement.
In data warehouses, where analytical queries on large datasets are common, materialized views can precompute and store aggregated data, making reports and dashboards load faster.
For applications that generate regular reports, materialized views can store pre-aggregated data, reducing the time it takes to produce reports.
Applications experiencing performance bottlenecks due to heavy read operations on complex queries can use materialized views to alleviate the load on the database.
While materialized views offer performance benefits, there are factors to consider:
Your choice depends on how often the underlying data changes and how fresh you need the data in the materialized view to be.
Different database platforms have their own implementations and capabilitiesβranging from simple indexed views to full-fledged materialized views with incremental refresh and query rewrite features. Below we explore how Oracle Database and Microsoft SQL Server approach materialized views.
Oracle Database offers robust support for materialized views, including features such as query rewrite, fast (incremental) refresh, and integration with advanced replication and data warehousing scenarios. These views can automatically refresh on demand or according to a schedule, and can even incorporate only the changes made since the last refresh for optimal performance.
To create a materialized view that can be refreshed incrementally (also known as a fast refresh), you define it with BUILD IMMEDIATE
to populate it immediately, and REFRESH FAST ON DEMAND
so it can be refreshed on command, only applying changes since the last refresh:
CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;
Oracle requires a materialized view log on the base table to track changes (inserts, updates, deletes) so that a fast refresh can apply only the incremental differences. The log records rowids and specified columns, capturing new values when data changes:
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (region, amount)
INCLUDING NEW VALUES;
In Microsoft SQL Server, the equivalent of a materialized view is an indexed view. An indexed view physically materializes the results of a query by creating a unique clustered index on the view definition. Unlike Oracle, SQL Server does not natively support incremental refresh on demandβdata changes to the underlying tables automatically propagate through the index during DML operations.
To define an indexed view in SQL Server, you must use WITH SCHEMABINDING
so the view is tied to the schema of the underlying tables, then create a unique clustered index on the view. This forces SQL Server to maintain the viewβs data as the base tables change:
CREATE VIEW dbo.sales_summary
WITH SCHEMABINDING AS
SELECT region, SUM(amount) AS total_sales, COUNT_BIG(*) AS count
FROM dbo.sales
GROUP BY region;
GO
CREATE UNIQUE CLUSTERED INDEX idx_sales_summary ON dbo.sales_summary(region);