Materialized Views in Oracle

TLDR

Oracle Materialized Views are designed to support data warehousing and replication. They can be defined on joins or subqueries, optionally aggregated, refreshed on transaction boundaries, on demand, or periodically, optimized for bulk loads with a multi‑MV scheduler, support bidirectional replication of remote tables, and enable transparent query rewrite via cost‑based selection using dimensions, lossless joins, functional dependencies, column equivalence, join derivability, joinback, and aggregate rollup.

Abstract

Oracle Materialized Views (MVs) are designed for data warehousing and replication. For data warehousing, MVs based on inner/outer equijoins with optional aggregation, can be refreshed on transaction boundaries, on demand, or periodically. Refreshes are optimized for bulk loads and can use a multi-MV scheduler. MVs based on subqueries on remote tables support bidirectional replication. Optimization with MVs includes transparent query rewrite based on costbased selection method. The ability to rewrite a large class of queries based on a small set of MVs is supported by using Dimensions (new Oracle object), losslessness of joins, functional dependency, column equivalence, join derivability, joinback and aggregate rollup.