Database Management SystemsViews

SQL Views: Virtual Tables and Data Abstraction

LevelIntermediate

Duration55 mins

TopicViews

5 / 5

Materialized Views: Pre-Computed Query Results

The Performance-Freshness Trade-off

Standard views are elegant but have a fundamental limitation: they recompute their results every time they're queried. For simple views, this is negligible. For complex aggregations over millions of rows, it becomes a performance bottleneck.

Imagine a dashboard showing year-to-date sales by region, category, and month—a query that joins five tables and aggregates billions of transactions. Running this query live takes 30 seconds. But the underlying data changes only hourly. Why recompute constantly?

Materialized views solve this by storing computed results physically. Query the view in milliseconds instead of minutes, accepting that data might be slightly stale. For many use cases, this trade-off is exactly right.

What You Will Learn

By the end of this page, you will understand what materialized views are, how they differ from standard views, when to use them, refresh strategies, indexing capabilities, and DBMS-specific implementations.

What Is a Materialized View?

A materialized view (also called an indexed view in SQL Server or snapshot in older Oracle terminology) is a database object that stores the results of a view query physically on disk. Unlike standard views that exist only as stored queries, materialized views contain actual data rows.

The key distinction:

Aspect	Standard View	Materialized View
Storage	Query definition only	Query definition + result data
Query execution	Computes results every time	Returns stored results instantly
Data freshness	Always current	Potentially stale (until refreshed)
Maintenance	None required	Refresh operations needed
Indexing	Not possible	Fully indexable
Space usage	Minimal	Proportional to result size

materialized_view_concept.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- PostgreSQL: Create materialized view
CREATE MATERIALIZED VIEW mv_sales_summary AS
    SELECT 
        p.category_id,
        DATE_TRUNC('month', s.sale_date) AS sale_month,
        COUNT(*) AS transaction_count,
        SUM(s.quantity) AS units_sold,
        SUM(s.amount) AS revenue,
        AVG(s.amount) AS avg_transaction
    FROM sales s
    JOIN products p ON s.product_id = p.id
    WHERE s.sale_date >= '2024-01-01'
    GROUP BY p.category_id, DATE_TRUNC('month', s.sale_date);
 
-- Query returns instantly (reads stored data, not base tables)
SELECT * FROM mv_sales_summary 
WHERE category_id = 5
ORDER BY sale_month;
 
-- Data is STALE until refreshed
REFRESH MATERIALIZED VIEW mv_sales_summary;
-- Now contains current data

Think of Materialized Views as Caching

A materialized view is essentially a database-level cache for query results. Like any cache, it trades freshness for speed. The key design decisions are: what to cache (query definition), when to invalidate (refresh strategy), and how long staleness is acceptable.

When to Use Materialized Views

Materialized views aren't always the right solution. They shine in specific scenarios and create problems in others.

Ideal use cases:

Expensive aggregations: Queries with GROUP BY over millions of rows
Complex joins: Multi-table joins that don't need real-time data
Data warehouse summaries: Pre-aggregated metrics for BI dashboards
Infrequently changing data: Reference data, historical aggregates
Read-heavy workloads: Many reads, few writes to source data
Remote data optimization: Cross-database or cross-server queries

Materialized View Decision Matrix
Factor	Favors Materialized View	Favors Standard View
Query complexity	High (many joins, aggregations)	Low (simple selects)
Query frequency	Frequent queries on same data	Infrequent or varied queries
Data change rate	Low (hourly, daily changes)	High (continuous transactions)
Freshness requirement	Tolerates minutes/hours of lag	Must be real-time
Query response time	Must be sub-second	Seconds acceptable
Storage budget	Available disk space	Storage constrained
Maintenance window	Available refresh time	No maintenance window

Anti-patterns (when NOT to use):

Real-time requirements: If users must see data changes immediately, staleness is unacceptable
Write-heavy workloads: If source data changes constantly, refresh overhead dominates
Small datasets: If the base query is already fast, materialization adds complexity without benefit
Limited refresh windows: If you can't refresh often enough, data becomes too stale
Rapidly evolving schemas: Materialized views are harder to modify than standard views

The Dashboard Sweet Spot

Dashboards are the classic materialized view use case. Users tolerate data that's an hour old in exchange for instant page loads. If your dashboard query takes 30 seconds live, a materialized view refreshed hourly transforms user experience dramatically.

Refresh Strategies

The refresh strategy determines how and when materialized view data gets updated. This is the critical design decision for materialized views—it balances freshness against performance and resource usage.

Complete (Full) Refresh:

The simplest strategy: delete all data in the materialized view and re-execute the query from scratch.

Pros: Simple, always correct, works for any query
Cons: Expensive for large datasets, locks during refresh
Use when: Dataset is small, or refresh window allows complete recomputation

refresh_strategies.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- PostgreSQL: Complete refresh
REFRESH MATERIALIZED VIEW mv_sales_summary;
 
-- With CONCURRENTLY: Allows queries during refresh (requires unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_summary;
 
 
-- Oracle: Complete refresh
BEGIN
    DBMS_MVIEW.REFRESH('MV_SALES_SUMMARY', 'C');  -- 'C' = Complete
END;
 
-- Oracle: Create with auto-refresh schedule
CREATE MATERIALIZED VIEW mv_sales_daily
    BUILD IMMEDIATE
    REFRESH COMPLETE
    START WITH SYSDATE
    NEXT SYSDATE + 1  -- Daily refresh
AS
    SELECT ... FROM sales GROUP BY ...;
 
 
-- SQL Server: Indexed views are always synchronized (no manual refresh)
-- For manual control, use tables + stored procedures instead

Incremental (Fast) Refresh:

Only apply changes since the last refresh, rather than recomputing everything.

Pros: Much faster, minimal locking, efficient for large views
Cons: Requires materialized view logs (Oracle) or change tracking, not all queries support it
Use when: Dataset is large, changes are small relative to total size

incremental_refresh.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Oracle: Incremental refresh setup
-- Step 1: Create materialized view log on base table(s)
CREATE MATERIALIZED VIEW LOG ON sales
    WITH PRIMARY KEY, ROWID
    INCLUDING NEW VALUES;
 
CREATE MATERIALIZED VIEW LOG ON products
    WITH PRIMARY KEY, ROWID;
 
-- Step 2: Create materialized view with FAST refresh
CREATE MATERIALIZED VIEW mv_sales_summary
    BUILD IMMEDIATE
    REFRESH FAST ON DEMAND  -- Incremental refresh
AS
    SELECT 
        p.category_id,
        COUNT(*) AS tx_count,
        SUM(s.amount) AS revenue
    FROM sales s
    JOIN products p ON s.product_id = p.id
    GROUP BY p.category_id;
 
-- Step 3: Refresh incrementally
BEGIN
    DBMS_MVIEW.REFRESH('MV_SALES_SUMMARY', 'F');  -- 'F' = Fast
END;
-- Only processes new/changed rows since last refresh!
 
 
-- Oracle: ON COMMIT refresh (synchronous)
CREATE MATERIALIZED VIEW mv_order_counts
    REFRESH FAST ON COMMIT  -- Auto-refresh on transaction commit
AS
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id;

Incremental Refresh Restrictions

Not all queries support incremental refresh. Oracle requires specific conditions: single-table or certain join types, specific aggregate functions, materialized view logs on all tables. Complex queries may only support complete refresh.

Indexing Materialized Views

One of the most powerful features of materialized views is their ability to be indexed. Unlike standard views (which have no physical rows), materialized views store data and can have indexes created on that data—accelerating queries even further.

Why index materialized views?

The materialized view stores pre-aggregated data, but you still need to filter and sort that data. An index on category_id means filtering by category is O(log n) instead of O(n). For large materialized views, this makes a significant difference.

indexing_materialized_views.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- PostgreSQL: Create indexes on materialized view
CREATE MATERIALIZED VIEW mv_daily_sales AS
    SELECT 
        sale_date,
        region_id,
        product_category,
        SUM(amount) AS revenue,
        COUNT(*) AS transactions
    FROM sales
    GROUP BY sale_date, region_id, product_category;
 
-- Add indexes for common query patterns
CREATE INDEX idx_mv_daily_sales_date ON mv_daily_sales (sale_date);
CREATE INDEX idx_mv_daily_sales_region ON mv_daily_sales (region_id);
CREATE INDEX idx_mv_daily_sales_category ON mv_daily_sales (product_category);
 
-- Composite index for common filter combination
CREATE INDEX idx_mv_daily_sales_region_date 
    ON mv_daily_sales (region_id, sale_date);
 
-- UNIQUE index required for CONCURRENTLY refresh
CREATE UNIQUE INDEX idx_mv_daily_sales_pk 
    ON mv_daily_sales (sale_date, region_id, product_category);
 
 
-- SQL Server: Indexed views (automatic materialization)
CREATE VIEW v_sales_by_region
WITH SCHEMABINDING  -- Required for indexed views
AS
    SELECT 
        region_id,
        product_category,
        SUM(amount) AS total_revenue,
        COUNT_BIG(*) AS row_count  -- Required for aggregates
    FROM dbo.sales
    GROUP BY region_id, product_category;
 
-- Create UNIQUE CLUSTERED index to materialize
CREATE UNIQUE CLUSTERED INDEX idx_v_sales_by_region 
    ON v_sales_by_region (region_id, product_category);
-- View is now materialized and auto-maintained!

SQL Server's Unique Approach

SQL Server doesn't have CREATE MATERIALIZED VIEW. Instead, you create a standard view WITH SCHEMABINDING, then add a unique clustered index. This transforms the view into an indexed view, which is materialized and maintained automatically on each DML operation.

Index maintenance trade-offs:

More indexes = faster queries but slower refreshes. Each index must be updated during refresh. For materialized views that refresh frequently, limit indexes to those truly needed.

Index strategy recommendations:

Query Pattern	Index Type	Example
Filter by dimension	Single-column B-tree	`CREATE INDEX ON mv (region_id)`
Date range queries	B-tree on date column	`CREATE INDEX ON mv (sale_date)`
Composite filters	Multi-column index	`CREATE INDEX ON mv (region_id, category)`
Concurrent refresh	Unique index required	`CREATE UNIQUE INDEX ON mv (pk_cols)`

Query Rewrite and Automatic Usage

In sophisticated DBMSs (Oracle, SQL Server Enterprise), the query optimizer can automatically use materialized views to answer queries—even when the query doesn't reference the view directly. This is called query rewrite.

How query rewrite works:

User submits query against base tables
Optimizer recognizes a materialized view could satisfy the query
Optimizer rewrites query to use materialized view instead
Query executes against pre-computed data
User gets result without knowing a materialized view was used

query_rewrite.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Oracle: Enable query rewrite
CREATE MATERIALIZED VIEW mv_monthly_sales
    BUILD IMMEDIATE
    REFRESH FAST ON DEMAND
    ENABLE QUERY REWRITE  -- Magic happens here
AS
    SELECT 
        DATE_TRUNC('month', sale_date) AS sale_month,
        region_id,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date), region_id;
 
-- User queries base table
SELECT DATE_TRUNC('month', sale_date), region_id, SUM(amount)
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', sale_date), region_id;
 
-- Oracle optimizer rewrites to:
SELECT sale_month, region_id, revenue
FROM mv_monthly_sales
WHERE sale_month >= '2024-01-01';
 
-- Dramatically faster, transparent to user!
 
 
-- Check if query rewrite occurred
EXPLAIN PLAN FOR
    SELECT /* your query */;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Look for "MAT_VIEW REWRITE ACCESS" in plan

Query Rewrite Capability by DBMS
DBMS	Query Rewrite Support	Configuration
Oracle	Full support	`ENABLE QUERY REWRITE` on view
SQL Server Enterprise	Automatically matches indexed views	No explicit configuration
PostgreSQL	Not built-in (extension available)	pg_hint_plan or custom logic
MySQL	Not supported	N/A

Rewrite Limitations

Query rewrite only works when the optimizer is confident the materialized view contains sufficient data to answer the query. Partial matches (query needs data not in view) or stale views (when QUERY_REWRITE_STALE = NONE) won't trigger rewrites.

DBMS-Specific Implementations

Materialized view implementations vary significantly across DBMSs. Understanding the differences is essential when working with specific platforms.

postgresql_matview.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Create materialized view
CREATE MATERIALIZED VIEW mv_name AS
    SELECT ... FROM ... GROUP BY ...;
 
-- Refresh (blocks queries during refresh)
REFRESH MATERIALIZED VIEW mv_name;
 
-- Concurrent refresh (requires unique index)
CREATE UNIQUE INDEX ON mv_name (key_cols);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_name;
 
-- Check if view needs refresh
SELECT relname, last_refresh 
FROM pg_catalog.pg_matviews 
WHERE schemaname = 'public';
 
-- No automatic refresh - use pg_cron or app scheduling
-- No incremental refresh - always complete

Performance Considerations

Materialized views offer dramatic query speedups but introduce their own performance considerations. Understanding these helps you design effective materialized view strategies.

Performance Trade-offs

•Query speed vs refresh cost — Pre-computation shifts work from query time to refresh time. Complex views may take minutes to refresh.
•Storage cost — Materialized views duplicate data. A view over billions of rows might need gigabytes of storage.
•Index maintenance — More indexes speed queries but slow refreshes. Find the right balance.
•Lock contention — Non-concurrent refresh may block queries. Use CONCURRENTLY where available.
•Write amplification — SQL Server indexed views update on every base table DML, adding overhead to writes.

performance_monitoring.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- PostgreSQL: Monitor materialized view size
SELECT 
    schemaname,
    matviewname,
    pg_size_pretty(pg_relation_size(schemaname || '.' || matviewname)) AS size
FROM pg_matviews
ORDER BY pg_relation_size(schemaname || '.' || matviewname) DESC;
 
-- Oracle: Refresh statistics
SELECT 
    mview_name,
    refresh_mode,
    last_refresh_type,
    last_refresh_date,
    staleness
FROM user_mviews;
 
-- SQL Server: Indexed view overhead analysis
SET STATISTICS IO ON;
-- Run DML and observe additional I/O from indexed view maintenance
 
-- Refresh duration tracking (PostgreSQL)
CREATE OR REPLACE FUNCTION refresh_with_timing(view_name TEXT)
RETURNS INTERVAL AS $$
DECLARE
    start_time TIMESTAMP;
    end_time TIMESTAMP;
BEGIN
    start_time := clock_timestamp();
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || view_name;
    end_time := clock_timestamp();
    RETURN end_time - start_time;
END;
$$ LANGUAGE plpgsql;
 
SELECT refresh_with_timing('mv_daily_sales');

Start Simple, Measure, Optimize

Begin with complete refresh on a schedule that matches business needs. Measure refresh duration, query performance, and staleness impact. Only add complexity (incremental refresh, more indexes, concurrent refresh) when measurements justify it.

Summary: Mastering Materialized Views

Materialized views complete our exploration of database views—from basic virtual tables to pre-computed, indexed, queryable data stores. Let's consolidate the key points:

Key Takeaways

•Materialized views store computed results — Physical data, not just query definitions, enabling instant query response.
•The trade-off is freshness vs speed — Data may be stale until refreshed; acceptable for many use cases like dashboards.
•Refresh strategies vary — Complete refresh (simple, slow) vs incremental (complex, fast). Choose based on data size and change rate.
•Materialized views can be indexed — Further accelerate queries on the pre-computed data with standard index types.
•Query rewrite enables transparency — Advanced DBMSs can use materialized views automatically, without query changes.
•DBMS implementations differ significantly — Oracle is most feature-rich; PostgreSQL is simpler; SQL Server uses indexed views; MySQL requires workarounds.
•Performance requires measurement — Refresh duration, storage cost, and write overhead must be monitored and balanced.

Module Complete:

You've now mastered SQL Views comprehensively—from basic definitions through CREATE VIEW syntax, strategic benefits, updatability rules, and finally materialized views for performance. Views are foundational to database architecture, and this knowledge will serve you across every database project.

Module Complete

Congratulations! You've completed Module 1: Views. You understand view fundamentals, creation syntax, strategic benefits, updatability rules, and materialized views. This comprehensive knowledge enables you to design and implement effective view strategies in production database systems.

5 / 5

Loading learning content...

Database Management SystemsViews

SQL Views: Virtual Tables and Data Abstraction

LevelIntermediate

Duration55 mins

TopicViews

5 / 5

Materialized Views: Pre-Computed Query Results

The Performance-Freshness Trade-off

What You Will Learn

What Is a Materialized View?

The key distinction:

Aspect	Standard View	Materialized View
Storage	Query definition only	Query definition + result data
Query execution	Computes results every time	Returns stored results instantly
Data freshness	Always current	Potentially stale (until refreshed)
Maintenance	None required	Refresh operations needed
Indexing	Not possible	Fully indexable
Space usage	Minimal	Proportional to result size

materialized_view_concept.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- PostgreSQL: Create materialized view
CREATE MATERIALIZED VIEW mv_sales_summary AS
    SELECT 
        p.category_id,
        DATE_TRUNC('month', s.sale_date) AS sale_month,
        COUNT(*) AS transaction_count,
        SUM(s.quantity) AS units_sold,
        SUM(s.amount) AS revenue,
        AVG(s.amount) AS avg_transaction
    FROM sales s
    JOIN products p ON s.product_id = p.id
    WHERE s.sale_date >= '2024-01-01'
    GROUP BY p.category_id, DATE_TRUNC('month', s.sale_date);
 
-- Query returns instantly (reads stored data, not base tables)
SELECT * FROM mv_sales_summary 
WHERE category_id = 5
ORDER BY sale_month;
 
-- Data is STALE until refreshed
REFRESH MATERIALIZED VIEW mv_sales_summary;
-- Now contains current data

Think of Materialized Views as Caching

When to Use Materialized Views

Materialized views aren't always the right solution. They shine in specific scenarios and create problems in others.

Ideal use cases:

Expensive aggregations: Queries with GROUP BY over millions of rows
Complex joins: Multi-table joins that don't need real-time data
Data warehouse summaries: Pre-aggregated metrics for BI dashboards
Infrequently changing data: Reference data, historical aggregates
Read-heavy workloads: Many reads, few writes to source data
Remote data optimization: Cross-database or cross-server queries

Materialized View Decision Matrix
Factor	Favors Materialized View	Favors Standard View
Query complexity	High (many joins, aggregations)	Low (simple selects)
Query frequency	Frequent queries on same data	Infrequent or varied queries
Data change rate	Low (hourly, daily changes)	High (continuous transactions)
Freshness requirement	Tolerates minutes/hours of lag	Must be real-time
Query response time	Must be sub-second	Seconds acceptable
Storage budget	Available disk space	Storage constrained
Maintenance window	Available refresh time	No maintenance window

Anti-patterns (when NOT to use):

Real-time requirements: If users must see data changes immediately, staleness is unacceptable
Write-heavy workloads: If source data changes constantly, refresh overhead dominates
Small datasets: If the base query is already fast, materialization adds complexity without benefit
Limited refresh windows: If you can't refresh often enough, data becomes too stale
Rapidly evolving schemas: Materialized views are harder to modify than standard views

The Dashboard Sweet Spot

Refresh Strategies

Complete (Full) Refresh:

The simplest strategy: delete all data in the materialized view and re-execute the query from scratch.

Pros: Simple, always correct, works for any query
Cons: Expensive for large datasets, locks during refresh
Use when: Dataset is small, or refresh window allows complete recomputation

refresh_strategies.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- PostgreSQL: Complete refresh
REFRESH MATERIALIZED VIEW mv_sales_summary;
 
-- With CONCURRENTLY: Allows queries during refresh (requires unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_summary;
 
 
-- Oracle: Complete refresh
BEGIN
    DBMS_MVIEW.REFRESH('MV_SALES_SUMMARY', 'C');  -- 'C' = Complete
END;
 
-- Oracle: Create with auto-refresh schedule
CREATE MATERIALIZED VIEW mv_sales_daily
    BUILD IMMEDIATE
    REFRESH COMPLETE
    START WITH SYSDATE
    NEXT SYSDATE + 1  -- Daily refresh
AS
    SELECT ... FROM sales GROUP BY ...;
 
 
-- SQL Server: Indexed views are always synchronized (no manual refresh)
-- For manual control, use tables + stored procedures instead

Incremental (Fast) Refresh:

Only apply changes since the last refresh, rather than recomputing everything.

Pros: Much faster, minimal locking, efficient for large views
Cons: Requires materialized view logs (Oracle) or change tracking, not all queries support it
Use when: Dataset is large, changes are small relative to total size

incremental_refresh.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Oracle: Incremental refresh setup
-- Step 1: Create materialized view log on base table(s)
CREATE MATERIALIZED VIEW LOG ON sales
    WITH PRIMARY KEY, ROWID
    INCLUDING NEW VALUES;
 
CREATE MATERIALIZED VIEW LOG ON products
    WITH PRIMARY KEY, ROWID;
 
-- Step 2: Create materialized view with FAST refresh
CREATE MATERIALIZED VIEW mv_sales_summary
    BUILD IMMEDIATE
    REFRESH FAST ON DEMAND  -- Incremental refresh
AS
    SELECT 
        p.category_id,
        COUNT(*) AS tx_count,
        SUM(s.amount) AS revenue
    FROM sales s
    JOIN products p ON s.product_id = p.id
    GROUP BY p.category_id;
 
-- Step 3: Refresh incrementally
BEGIN
    DBMS_MVIEW.REFRESH('MV_SALES_SUMMARY', 'F');  -- 'F' = Fast
END;
-- Only processes new/changed rows since last refresh!
 
 
-- Oracle: ON COMMIT refresh (synchronous)
CREATE MATERIALIZED VIEW mv_order_counts
    REFRESH FAST ON COMMIT  -- Auto-refresh on transaction commit
AS
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id;

Incremental Refresh Restrictions

Indexing Materialized Views

Why index materialized views?

indexing_materialized_views.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- PostgreSQL: Create indexes on materialized view
CREATE MATERIALIZED VIEW mv_daily_sales AS
    SELECT 
        sale_date,
        region_id,
        product_category,
        SUM(amount) AS revenue,
        COUNT(*) AS transactions
    FROM sales
    GROUP BY sale_date, region_id, product_category;
 
-- Add indexes for common query patterns
CREATE INDEX idx_mv_daily_sales_date ON mv_daily_sales (sale_date);
CREATE INDEX idx_mv_daily_sales_region ON mv_daily_sales (region_id);
CREATE INDEX idx_mv_daily_sales_category ON mv_daily_sales (product_category);
 
-- Composite index for common filter combination
CREATE INDEX idx_mv_daily_sales_region_date 
    ON mv_daily_sales (region_id, sale_date);
 
-- UNIQUE index required for CONCURRENTLY refresh
CREATE UNIQUE INDEX idx_mv_daily_sales_pk 
    ON mv_daily_sales (sale_date, region_id, product_category);
 
 
-- SQL Server: Indexed views (automatic materialization)
CREATE VIEW v_sales_by_region
WITH SCHEMABINDING  -- Required for indexed views
AS
    SELECT 
        region_id,
        product_category,
        SUM(amount) AS total_revenue,
        COUNT_BIG(*) AS row_count  -- Required for aggregates
    FROM dbo.sales
    GROUP BY region_id, product_category;
 
-- Create UNIQUE CLUSTERED index to materialize
CREATE UNIQUE CLUSTERED INDEX idx_v_sales_by_region 
    ON v_sales_by_region (region_id, product_category);
-- View is now materialized and auto-maintained!

SQL Server's Unique Approach

Index maintenance trade-offs:

More indexes = faster queries but slower refreshes. Each index must be updated during refresh. For materialized views that refresh frequently, limit indexes to those truly needed.

Index strategy recommendations:

Query Pattern	Index Type	Example
Filter by dimension	Single-column B-tree	`CREATE INDEX ON mv (region_id)`
Date range queries	B-tree on date column	`CREATE INDEX ON mv (sale_date)`
Composite filters	Multi-column index	`CREATE INDEX ON mv (region_id, category)`
Concurrent refresh	Unique index required	`CREATE UNIQUE INDEX ON mv (pk_cols)`

Query Rewrite and Automatic Usage

How query rewrite works:

User submits query against base tables
Optimizer recognizes a materialized view could satisfy the query
Optimizer rewrites query to use materialized view instead
Query executes against pre-computed data
User gets result without knowing a materialized view was used

query_rewrite.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Oracle: Enable query rewrite
CREATE MATERIALIZED VIEW mv_monthly_sales
    BUILD IMMEDIATE
    REFRESH FAST ON DEMAND
    ENABLE QUERY REWRITE  -- Magic happens here
AS
    SELECT 
        DATE_TRUNC('month', sale_date) AS sale_month,
        region_id,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date), region_id;
 
-- User queries base table
SELECT DATE_TRUNC('month', sale_date), region_id, SUM(amount)
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', sale_date), region_id;
 
-- Oracle optimizer rewrites to:
SELECT sale_month, region_id, revenue
FROM mv_monthly_sales
WHERE sale_month >= '2024-01-01';
 
-- Dramatically faster, transparent to user!
 
 
-- Check if query rewrite occurred
EXPLAIN PLAN FOR
    SELECT /* your query */;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Look for "MAT_VIEW REWRITE ACCESS" in plan

Query Rewrite Capability by DBMS
DBMS	Query Rewrite Support	Configuration
Oracle	Full support	`ENABLE QUERY REWRITE` on view
SQL Server Enterprise	Automatically matches indexed views	No explicit configuration
PostgreSQL	Not built-in (extension available)	pg_hint_plan or custom logic
MySQL	Not supported	N/A

Rewrite Limitations

DBMS-Specific Implementations

Materialized view implementations vary significantly across DBMSs. Understanding the differences is essential when working with specific platforms.

postgresql_matview.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Create materialized view
CREATE MATERIALIZED VIEW mv_name AS
    SELECT ... FROM ... GROUP BY ...;
 
-- Refresh (blocks queries during refresh)
REFRESH MATERIALIZED VIEW mv_name;
 
-- Concurrent refresh (requires unique index)
CREATE UNIQUE INDEX ON mv_name (key_cols);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_name;
 
-- Check if view needs refresh
SELECT relname, last_refresh 
FROM pg_catalog.pg_matviews 
WHERE schemaname = 'public';
 
-- No automatic refresh - use pg_cron or app scheduling
-- No incremental refresh - always complete

Performance Considerations

Materialized views offer dramatic query speedups but introduce their own performance considerations. Understanding these helps you design effective materialized view strategies.

Performance Trade-offs

•Query speed vs refresh cost — Pre-computation shifts work from query time to refresh time. Complex views may take minutes to refresh.
•Storage cost — Materialized views duplicate data. A view over billions of rows might need gigabytes of storage.
•Index maintenance — More indexes speed queries but slow refreshes. Find the right balance.
•Lock contention — Non-concurrent refresh may block queries. Use CONCURRENTLY where available.
•Write amplification — SQL Server indexed views update on every base table DML, adding overhead to writes.

performance_monitoring.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- PostgreSQL: Monitor materialized view size
SELECT 
    schemaname,
    matviewname,
    pg_size_pretty(pg_relation_size(schemaname || '.' || matviewname)) AS size
FROM pg_matviews
ORDER BY pg_relation_size(schemaname || '.' || matviewname) DESC;
 
-- Oracle: Refresh statistics
SELECT 
    mview_name,
    refresh_mode,
    last_refresh_type,
    last_refresh_date,
    staleness
FROM user_mviews;
 
-- SQL Server: Indexed view overhead analysis
SET STATISTICS IO ON;
-- Run DML and observe additional I/O from indexed view maintenance
 
-- Refresh duration tracking (PostgreSQL)
CREATE OR REPLACE FUNCTION refresh_with_timing(view_name TEXT)
RETURNS INTERVAL AS $$
DECLARE
    start_time TIMESTAMP;
    end_time TIMESTAMP;
BEGIN
    start_time := clock_timestamp();
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || view_name;
    end_time := clock_timestamp();
    RETURN end_time - start_time;
END;
$$ LANGUAGE plpgsql;
 
SELECT refresh_with_timing('mv_daily_sales');

Start Simple, Measure, Optimize

Summary: Mastering Materialized Views

Materialized views complete our exploration of database views—from basic virtual tables to pre-computed, indexed, queryable data stores. Let's consolidate the key points:

Key Takeaways

•Materialized views store computed results — Physical data, not just query definitions, enabling instant query response.
•The trade-off is freshness vs speed — Data may be stale until refreshed; acceptable for many use cases like dashboards.
•Refresh strategies vary — Complete refresh (simple, slow) vs incremental (complex, fast). Choose based on data size and change rate.
•Materialized views can be indexed — Further accelerate queries on the pre-computed data with standard index types.
•Query rewrite enables transparency — Advanced DBMSs can use materialized views automatically, without query changes.
•DBMS implementations differ significantly — Oracle is most feature-rich; PostgreSQL is simpler; SQL Server uses indexed views; MySQL requires workarounds.
•Performance requires measurement — Refresh duration, storage cost, and write overhead must be monitored and balanced.

Module Complete:

Module Complete

5 / 5