Create Materialized Views for Summary Data

In modern Oracle databases, a materialized view is a powerful way to precompute summary data and store it physically on disk. Instead of recalculating aggregates (such as monthly or yearly totals) for every report, you can:

• Run the aggregation once into a materialized view, and
• Refresh it on a schedule or on demand.

This lesson focuses on how to write SQL that creates materialized views designed specifically for summary data.

Suppose you have a SALES table with detailed transaction rows:

• PRODUCT_ID – product being sold
• SALES_DATE – date of the sale
• SALES_AMOUNT – monetary amount of the sale

You want a materialized view that summarizes total sales by product and year:

CREATE MATERIALIZED VIEW sales_summary_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT
    product_id,
    EXTRACT(YEAR FROM sales_date) AS sales_year,
    SUM(sales_amount)            AS total_sales
FROM sales
GROUP BY
    product_id,
    EXTRACT(YEAR FROM sales_date);

Explanation

1. CREATE MATERIALIZED VIEW sales_summary_mv Creates a materialized view named SALES_SUMMARY_MV in the current schema.
2. BUILD IMMEDIATE Populates the materialized view as soon as the statement completes.
3. REFRESH FAST ON COMMIT
   • FAST – uses incremental changes (if the necessary preconditions are met), instead of recomputing the entire summary.
   • ON COMMIT – refreshes the view whenever a transaction that modifies SALES commits.
4. ENABLE QUERY REWRITE Allows the optimizer to transparently substitute this materialized view when a query against SALES can be answered by the summary data.
5. AS SELECT … GROUP BY … Aggregates SALES_AMOUNT by product and calendar year. This SELECT defines both the shape of the materialized view and its data.

For REFRESH FAST to work on a summary materialized view, Oracle requires additional metadata on the base table(s). A common pattern is to create a materialized view log on the SALES table:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (sales_date, product_id, sales_amount)
INCLUDING NEW VALUES;

Key points:

• Materialized view log – tracks row-level changes on SALES so Oracle can apply only the delta to the summary.
• WITH ROWID, SEQUENCE – improves the ability to perform fast refresh efficiently.
• Column list – include all columns referenced in the materialized view query (join columns, filter columns, and aggregated expressions).
• Constraints – the base table should have an appropriate primary key or unique constraint to support refresh and query rewrite.

If these conditions are not met, Oracle may fall back to a complete refresh or reject the FAST option.

When you design a materialized view for summary data, the workflow typically looks like this:

1. Identify the candidate measure Find numeric columns that are frequently aggregated, such as SALE_TOTAL, SALES_AMOUNT, or QUANTITY.
2. Choose the summary grain Decide at what level you want to store the summary: by month, by year, by region, by product, etc. For example, monthly sales by product is a different materialized view than yearly sales by region.
3. Decide on refresh options
   • BUILD – typically BUILD IMMEDIATE for an initial load.
   • REFRESH method – FAST, COMPLETE, or FORCE.
   • REFRESH timing – ON COMMIT (for near real-time) or ON DEMAND (for batch refresh during low-load periods).
   • QUERY REWRITE – usually ENABLE QUERY REWRITE for performance gains in analytic queries.
4. Write the SELECT statement Include:
   • Grouping keys (e.g., month, product category).
   • Aggregate functions (SUM, COUNT, AVG, etc.).
   • Optional filters (such as a date range).
5. Create the materialized view Combine the clauses with your SELECT to form the final CREATE MATERIALIZED VIEW statement.

In the sample schema, assume a SALE_HEADER table with:

• SALES_DATE – date the sale occurred.
• SALE_TOTAL – total amount for the sale.

You might want monthly totals and a row count for each month:

CREATE MATERIALIZED VIEW monthly_sales_mv
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT
    TO_CHAR(sales_date, 'YY-MONTH') AS sales_month,
    SUM(sale_total)                 AS total_sales,
    COUNT(*)                        AS transaction_count
FROM sale_header
GROUP BY
    TO_CHAR(sales_date, 'YY-MONTH');

Notes:

• REFRESH FORCE ON DEMAND
  • FORCE – tries FAST refresh first; if that is not possible, it falls back to COMPLETE refresh.
  • ON DEMAND – refreshes occur only when you explicitly request them (for example, via a job using DBMS_MVIEW.REFRESH).
• COUNT(*) For aggregate materialized views, including COUNT(*) is a common best practice and is often required for certain fast refresh scenarios, because it helps Oracle track row counts accurately.
• ENABLE QUERY REWRITE Analytic queries that request monthly totals can now be satisfied from MONTHLY_SALES_MV instead of scanning the transactional SALE_HEADER table.

Although this lesson focuses on summary data, the same pattern applies when your summary spans multiple tables. For example, you might summarize sales by product and region using SALE_HEADER, SALE_DETAIL, and PRODUCT. In that case:

• The SELECT in the materialized view definition will include joins.
• Each base table participating in a fast refresh join-only materialized view typically needs a materialized view log.
• Oracle may require ROWID-based logs and appropriate indexes on the join columns for efficient refresh.

The overall creation steps, identify the candidate, choose refresh options, write the SELECT, and create the materialized view—remain the same.

Click the Exercise link below to practice creating a materialized view for aggregate data.
Create Materialized View - Exercise

[1]ROWID: An internal value that uniquely identifies each individual row in an Oracle database.