MATERIALIZED VIEWDDL
Buy Me A Coffee

A view whose result set is physically stored on disk and can be refreshed on demand or automatically. Trades storage and staleness for query speed — avoids recomputing expensive aggregations on every query.

Physical snapshots of a query result stored as a table, refreshed on demand or on a schedule. Dramatically faster for expensive aggregations over large tables at the cost of stale data. The right choice when near-real-time accuracy is not required.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — MySQL has no native materialized view. Common workarounds: (1) a regular table populated by a scheduled event or stored procedure; (2) a STORED table populated by a trigger on the source table. MariaDB does not have materialized views either.
PostgreSQL ✓ Supported 9.3 REFRESH MATERIALIZED VIEW rebuilds the view from scratch (locks table during refresh by default). REFRESH MATERIALIZED VIEW CONCURRENTLY (requires a UNIQUE index) allows concurrent reads during refresh. No automatic refresh — use pg_cron, a trigger, or an external scheduler. Can be indexed like a regular table.
SQL Server ✓ Supported 2000 Called indexed views. Create a regular view, then add a UNIQUE CLUSTERED INDEX — SQL Server automatically maintains the stored result on DML changes (synchronous, not on-demand). Requires the view to be created WITH SCHEMABINDING and imposes restrictions: no subqueries, no OUTER JOINs (Enterprise Edition relaxes some), no non-deterministic functions. The optimizer can use indexed views automatically (Enterprise) or with NOEXPAND hint (all editions).
Oracle ✓ Supported 8i Full-featured materialized views with multiple refresh modes: COMPLETE (full recompute), FAST (incremental via materialized view logs), and FORCE (FAST if possible, else COMPLETE). Refresh can be ON COMMIT (synchronous) or ON DEMAND. The query rewrite feature lets Oracle's optimizer automatically use a materialized view instead of the base tables when it detects a matching pattern.
SQLite ✗ Not Supported — SQLite has no materialized view support. The standard workaround is to create a regular table and populate it manually or via a trigger.

Details

The concept is consistent but implementations vary enormously. SQL Server's indexed views are automatically maintained on every DML — no REFRESH command exists. Oracle supports incremental (FAST) refresh via materialized view logs, avoiding full recomputes. PostgreSQL's REFRESH is always a full recompute; CONCURRENTLY mode avoids locking but needs a unique index. MySQL has no native support at all. Oracle's query rewrite feature is unique: the optimizer can transparently substitute the materialized view for the base tables in unmodified queries.

Standard Syntax

-- PostgreSQL: CREATE MATERIALIZED VIEW monthly_sales AS SELECT date_trunc('month', order_date) AS month, SUM(amount) AS total FROM orders GROUP BY 1; REFRESH MATERIALIZED VIEW monthly_sales;

Version Support

MySQL: Not supported PostgreSQL: Since 9.3 SQL Server: Since 2000 Oracle: Since 8i SQLite: Not supported

Per-Database Syntax & Notes

MySQL

MySQL has no native materialized view. Common workarounds: (1) a regular table populated by a scheduled event or stored procedure; (2) a STORED table populated by a trigger on the source table. MariaDB does not have materialized views either.

-- Workaround: populate a summary table via event: CREATE TABLE monthly_sales_cache ( month DATE PRIMARY KEY, total DECIMAL(15,2) ); CREATE EVENT refresh_monthly_sales ON SCHEDULE EVERY 1 HOUR DO BEGIN TRUNCATE TABLE monthly_sales_cache; INSERT INTO monthly_sales_cache SELECT DATE_FORMAT(order_date,'%Y-%m-01'), SUM(amount) FROM orders GROUP BY 1; END;

PostgreSQL

REFRESH MATERIALIZED VIEW rebuilds the view from scratch (locks table during refresh by default). REFRESH MATERIALIZED VIEW CONCURRENTLY (requires a UNIQUE index) allows concurrent reads during refresh. No automatic refresh — use pg_cron, a trigger, or an external scheduler. Can be indexed like a regular table.

CREATE MATERIALIZED VIEW monthly_sales AS SELECT date_trunc('month', order_date) AS month, SUM(amount) AS total FROM orders GROUP BY 1 WITH DATA; -- populate immediately (default) -- Without initial data: CREATE MATERIALIZED VIEW monthly_sales AS ... WITH NO DATA; -- Refresh (blocking): REFRESH MATERIALIZED VIEW monthly_sales; -- Refresh without locking reads (requires unique index): CREATE UNIQUE INDEX ON monthly_sales (month); REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales; -- Drop: DROP MATERIALIZED VIEW monthly_sales;

SQL Server

Called indexed views. Create a regular view, then add a UNIQUE CLUSTERED INDEX — SQL Server automatically maintains the stored result on DML changes (synchronous, not on-demand). Requires the view to be created WITH SCHEMABINDING and imposes restrictions: no subqueries, no OUTER JOINs (Enterprise Edition relaxes some), no non-deterministic functions. The optimizer can use indexed views automatically (Enterprise) or with NOEXPAND hint (all editions).

-- Step 1: create view with SCHEMABINDING: CREATE VIEW dbo.monthly_sales WITH SCHEMABINDING AS SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0) AS month, SUM(amount) AS total, COUNT_BIG(*) AS cnt -- required by indexed view rules FROM dbo.orders GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0); -- Step 2: add unique clustered index to materialize: CREATE UNIQUE CLUSTERED INDEX idx_mv ON dbo.monthly_sales (month); -- SQL Server auto-maintains the index on INSERT/UPDATE/DELETE. -- No manual REFRESH needed or available.

Oracle

Full-featured materialized views with multiple refresh modes: COMPLETE (full recompute), FAST (incremental via materialized view logs), and FORCE (FAST if possible, else COMPLETE). Refresh can be ON COMMIT (synchronous) or ON DEMAND. The query rewrite feature lets Oracle's optimizer automatically use a materialized view instead of the base tables when it detects a matching pattern.

-- On-demand complete refresh: CREATE MATERIALIZED VIEW monthly_sales BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT TRUNC(order_date,'MM') AS month, SUM(amount) AS total FROM orders GROUP BY TRUNC(order_date,'MM'); EXEC DBMS_MVIEW.REFRESH('monthly_sales'); -- Fast (incremental) refresh requires a log on the base table: CREATE MATERIALIZED VIEW LOG ON orders WITH ROWID, SEQUENCE (order_date, amount) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW monthly_sales REFRESH FAST ON COMMIT AS ...; -- Automatic query rewrite (Enterprise): CREATE MATERIALIZED VIEW monthly_sales ENABLE QUERY REWRITE AS ...;

SQLite

SQLite has no materialized view support. The standard workaround is to create a regular table and populate it manually or via a trigger.

-- Workaround: populate a summary table: CREATE TABLE monthly_sales_cache ( month TEXT PRIMARY KEY, total REAL ); -- Refresh manually: DELETE FROM monthly_sales_cache; INSERT INTO monthly_sales_cache SELECT strftime('%Y-%m', order_date), SUM(amount) FROM orders GROUP BY strftime('%Y-%m', order_date);