Oracle Materialized Views: Caching Complex Queries

What materialized views are

A materialized view is a database object that physically stores the result of a query. Unlike regular views (which execute the query each time), materialized views return pre-computed results instantly. They’re refreshed periodically or on demand to stay in sync with source tables.

Basic creation

CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE            -- build at creation time (vs DEFERRED)
REFRESH COMPLETE           -- refresh strategy
ON DEMAND                  -- refresh trigger
ENABLE QUERY REWRITE       -- allow optimizer to use this MV for queries on base tables
AS
SELECT
  department_id,
  TRUNC(sale_date, 'MM') AS sale_month,
  SUM(amount) AS total_sales,
  COUNT(*) AS transaction_count
FROM sales
GROUP BY department_id, TRUNC(sale_date, 'MM');

Refresh strategies

COMPLETE: truncates and re-populates from scratch. Safe for any query but can be slow for large MVs.

FAST (incremental): applies only changes since last refresh using materialized view logs. Much faster but requires CREATE MATERIALIZED VIEW LOG on source tables and query restrictions.

FORCE: uses FAST if possible, falls back to COMPLETE.

-- Create log on source table for fast refresh:
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (department_id, sale_date, amount)
INCLUDING NEW VALUES;

Refresh scheduling

-- Automatic refresh every night at 2am:
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH COMPLETE
START WITH TRUNC(SYSDATE) + 26/24  -- 2am today
NEXT TRUNC(SYSDATE+1) + 26/24     -- 2am each subsequent day
AS ...;

Query rewrite: transparent acceleration

When ENABLE QUERY REWRITE is set, Oracle automatically rewrites queries on base tables to use the MV:

-- This query on the base table...
SELECT department_id, SUM(amount) FROM sales GROUP BY department_id;

-- ...is automatically rewritten to:
SELECT department_id, total_sales FROM mv_sales_summary;

Users and applications get fast results without changing their queries.

When to use materialized views

• Nightly or hourly summary reports run against large OLTP tables
• Cross-instance joins (remote database links involved)
• Queries that the optimizer consistently executes badly due to data complexity
• When you can’t add indexes to source tables (production OLTP systems)