# Materialized View
A **materialized view** is a database object containing precomputed query results, physically stored on disk. Unlike a regular (virtual) view which re-executes its query on every access, a materialized view serves reads directly from stored data.
## Regular View vs Materialized View
| Regular View | Materialized View |
|---|---|
| Virtual — no stored data | Physical — data stored on disk |
| Query re-executed on every access | Precomputed, reads from stored result |
| Always up to date | May become stale (needs refresh) |
| No storage overhead | Uses disk space |
| Can be slow for complex queries | Fast reads, even for complex queries |
## When to Use
- **Expensive aggregations** — `GROUP BY`, `SUM`, `COUNT`, `AVG` over large tables
- **Complex joins** — queries joining many tables, read frequently but changed infrequently
- **Reporting and analytics** — dashboards tolerating slightly stale data
- **Remote data** — caching results of queries over database links or federated sources
- **Data warehousing** — precomputing summaries of fact tables
## Refresh Strategies
- **Complete refresh** — drops and rebuilds entirely. Simple but expensive.
- **Incremental (fast) refresh** — applies only changes since last refresh using a change log. Requires materialized view logs on base tables.
- **On commit** — automatically refreshed on affecting transaction commits. Always fresh but adds write overhead.
- **On demand** — manually or on schedule (e.g., nightly). Trades freshness for write performance.
## Examples
**PostgreSQL:**
```sql
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
date_trunc('month', order_date) AS month,
SUM(amount) AS total_sales,
COUNT(*) AS order_count
FROM orders
GROUP BY date_trunc('month', order_date);
-- Refresh
REFRESH MATERIALIZED VIEW monthly_sales;
-- Concurrent refresh (doesn't lock reads, requires unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
```
**Oracle:**
```sql
CREATE MATERIALIZED VIEW monthly_sales
REFRESH FAST ON COMMIT
AS
SELECT ... ;
```
Oracle supports automatic query rewrite, where the optimizer transparently uses a materialized view to answer queries against base tables.
## Indexing
Materialized views can be indexed like regular tables:
```sql
CREATE INDEX idx_monthly_sales_month ON monthly_sales (month);
```
---
See also: [[ACID]], [[Database Transaction Schedule]]