What Is a Materialized View?

A materialized view is a database object that stores the physical result of a query. Unlike a regular (logical) view, which only keeps the SQL definition and executes it each time you query it, a materialized view persists the data on disk.

In Oracle (including 12c, 19c, and newer releases), materialized views are widely used to:

• Precompute expensive joins and aggregations.
• Cache data from remote databases for faster local access.
• Provide summary tables for reporting and data warehousing.

When query rewrite is enabled, the cost-based optimizer can transparently redirect a user query to an appropriate materialized view instead of scanning the underlying base tables. The application still queries the base tables in its SQL, but the optimizer substitutes the materialized view when it can prove that the results are equivalent.

At a high level, a materialized view:

• Runs a query against one or more base tables or views.
• Stores the result set physically in its own segment.
• Refreshes that stored data on a schedule or when explicitly requested.

Because the results are precomputed, user queries against the materialized view are often much faster than running the same logic against detailed transactional tables.

Materialized views are useful in several common scenarios:

Area	Benefit
Data warehousing	Improves performance by storing pre-computed aggregates (for example, sales by month, region, or product), which are frequently required along dimension hierarchies. Queries can use these summaries instead of scanning large fact tables.
Distributed databases	Acts as a snapshot or replicated copy of data from a master site. Remote or reporting databases can query the local materialized view instead of crossing the network for every request.
Mobile / remote computing	Provides replicated and aggregated data for laptops or edge systems that may not always be connected to the primary database. Materialized views can be refreshed when connectivity is available.

In all of these areas, materialized views help reduce I/O, CPU usage, and network traffic by moving work from query time to refresh time.

Conceptually, you can think of a simple local copy of a remote table as being created with a command like this:

CREATE TABLE local_bookshelf AS
SELECT *
FROM   bookshelf@remote_connect;

This statement copies data from a remote table into a local table. While this can boost performance for some distributed queries, it has several drawbacks:

• The local copy becomes out of date as soon as changes are made to the remote master table.
• Local users could update the copy, making it diverge even further from the master data.
• You must manage your own refresh logic if you want to keep the copy synchronized.

A materialized view solves these problems by:

• Storing the query definition and its result set together.
• Providing built-in refresh mechanisms (FAST, COMPLETE, FORCE) on a schedule or on demand.
• Optionally preventing local updates so the view remains a read-only replica or summary.
• Supporting transactional or incremental refresh, where only changed rows are propagated from the master to the materialized view, reducing refresh overhead.

In decision support environments, it is common to:

• Use complex queries to periodically roll up detailed data into summary materialized views.
• Restrict rows (for example, by date range or business unit) or columns (only those required for reporting) in the materialized view.
• Apply functions in the defining query to compute new derived columns used by analysts and reports.

By shifting heavy computation into scheduled refresh operations, materialized views make day-to-day queries faster and more predictable. In the next lesson, you will see the basic syntax for creating materialized views and how refresh strategies influence performance.