Materialized View

A materialized view stores the physical result of a query, unlike a normal view which re-runs each time. It is refreshed on demand or on a schedule, trading freshness for much faster reads.

Materialized views are ideal for expensive aggregations queried far more often than the underlying data changes — dashboards and reports, for example. Oracle can even rewrite queries to use a materialized view automatically.

The cost is staleness and storage: results are only as current as the last refresh, and the data is duplicated on disk.

Example

CREATE MATERIALIZED VIEW dept_totals AS
SELECT department_id, COUNT(*) AS headcount, SUM(salary) AS payroll
FROM   employees
GROUP BY department_id;