Materialized Views for Reporting

Why Materialize

A materialized view stores query results as real data. Reporting queries read the stored snapshot instead of replaying expensive joins and aggregations.

Creating One

CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND AS
SELECT region, SUM(amount) AS total
FROM sales GROUP BY region;

Refreshing

BEGIN
  DBMS_MVIEW.REFRESH('MV_SALES_SUMMARY');
END;

REFRESH FAST with a materialized view log applies only the changed rows. REFRESH COMPLETE rebuilds the whole snapshot.

Query Rewrite

Enable query rewrite so the optimizer can use the materialized view even when your SQL queries the base tables directly.

Key Points

• Snapshots make reporting queries fast.
• Refresh COMPLETE or FAST as the data demands.
• Use ON COMMIT for near-real-time summaries.
• Query rewrite uses the view transparently.