Lesson 1

Modern Oracle databases provide several features that help you speed up queries on large data sets, especially in data warehouse and reporting environments. One of the most important of these features is the materialized view, which stores precomputed results—aggregations, joins, and filtered subsets of data—and allows the optimizer to reuse these results instead of re-running expensive queries.

Materialized views are particularly effective in schemas that resemble a data warehouse^[1], where fact tables can contain millions (or billions) of rows and queries frequently group data by time, geography, or product dimensions. By storing summary data in materialized views and enabling query rewrite, Oracle’s cost-based optimizer can transparently redirect user queries to these prebuilt summaries, often improving performance by orders of magnitude.

In this module, you will learn how materialized views fit into the broader performance-tuning toolbox and how to design and manage them effectively.

Module Objectives
In this module, you will learn how to:

1. Describe what a materialized view is and when to use it.
2. Explain the key clauses and parameters in the CREATE MATERIALIZED VIEW statement.
3. Write SQL to create materialized views that store summary and replicated data.
4. Describe indexing strategies for materialized views and how they affect performance.
5. Check materialized view status, troubleshoot invalid or stale views, and repair them.
6. Describe the conditions that allow the optimizer to perform query rewrite using materialized views.
7. Describe the role of packages such as DBMS_OLAP and DBMS_MVIEW in managing summaries and refresh operations.

A materialized view is a database object that stores the result of a query physically on disk. Unlike a normal (logical) view, which is just a stored SELECT statement, a materialized view keeps a copy of the data. This data can be:

• A summarized version of a fact table (for example, sales by month and region).
• A join of multiple tables (for example, sales joined to products and customers).
• A replicated subset of data from a remote or primary database.

For data warehouse workloads, materialized views are commonly used to:

• Precompute GROUP BY aggregations at different levels in a hierarchy (day, month, quarter, year).
• Store rollup or cube-style summaries that many users query repeatedly.
• Provide local copies of remote tables to avoid constant distributed queries.

When query rewrite is enabled, Oracle’s cost-based optimizer can automatically substitute a materialized view in place of a detailed fact table when it can prove that the materialized view contains all the information needed for the query. For example, a report that summarizes quarterly revenue may be answered by reading a monthly sales summary materialized view and then aggregating those results to the quarter level, rather than scanning the base transaction table.

With each major release (10g, 11g, 12c, 19c, and into 23c/23ai), Oracle has improved how the optimizer discovers, selects, and rewrites queries to use eligible materialized views.

The SQL Tuning Advisor and Automatic SQL Tuning were introduced in Oracle Database 10g and continue to play a central role in Oracle 11g Release 2 and the cloud-enabled databases from 12c through 19c (including Autonomous Database offerings).

Oracle 11g R2

• Automatic SQL Tuning tasks typically run during nightly maintenance windows.
• Problematic SQL statements are identified from the workload (e.g., AWR snapshots) and fed to the tuning engine.
• Recommendations may include creating SQL profiles, adding or modifying indexes, restructuring queries, or—where appropriate—introducing materialized views.

Oracle 12c to 19c and Cloud-Enabled Databases

• SQL Tuning Advisor is tightly integrated with:
  • Automatic Workload Repository (AWR) for historical performance data.
  • SQL Plan Baselines to preserve stable, known-good execution plans.
  • Adaptive Query Optimization to refine estimates and join methods at runtime.
• In cloud deployments and Autonomous Database, many tuning operations can be fully or partially automated. The system may:
  • Auto-accept certain SQL profiles.
  • Recommend or automatically use new access structures.
  • Leverage machine learning to detect regressing SQL statements.

Across these versions, the tuning advisors help you choose where materialized views (and their refresh strategies) make the most sense: frequently executed, resource-intensive queries, especially those with predictable aggregation and join patterns.

While materialized views are often associated with classical data warehouses and star schemas, they are equally useful in other scenarios:

• Snapshot replication for distributed sites: Local materialized views can hold copies of remote data, reducing network round trips for read-heavy workloads.
• Mobile and disconnected environments: Extracts of key tables can be stored as materialized views on edge or mobile systems and refreshed periodically.
• Reporting and BI environments: Complex reports can be redirected to summary materialized views rather than base transactional tables.

To improve application performance, you can:

• Create local copies of remote tables or subsets of those tables.
• Build summary materialized views using GROUP BY against large transaction tables.
• Schedule automatic refreshes (for example, hourly or nightly) to keep summaries reasonably current while avoiding excessive refresh overhead.

In this module, you will see:

• The general usage patterns for materialized views.
• Refresh options (FAST vs. COMPLETE, ON COMMIT vs. ON DEMAND, scheduled refresh).
• How materialized views interact with query rewrite and the cost-based optimizer.

The next lesson will introduce the core syntax and examples of materialized view definitions.

[1] Data Warehouse: A type of database used primarily for analysis and reporting. Data warehouses frequently employ star schemas rather than highly normalized transactional schemas and are often kept separate from OLTP systems.

[2] SQL Advisor: An Oracle Database feature (SQL Tuning Advisor and related tools) that performs in-depth optimization analysis on selected SQL statements or workloads and suggests actions such as statistics collection, SQL profiles, indexes, and materialized views.