Refreshing Materialized Views

Materialized views are an advanced feature of SQL that store the results of a query in a database. Unlike standard views, which always present up-to-date data from the underlying tables, materialized views capture data at a specific point in time. To ensure the accuracy and relevance of the data, it is necessary to refresh materialized views periodically or as required.

What is Refreshing in Materialized Views?

Refreshing a materialized view means updating its contents to reflect changes made in the underlying base tables. Refreshing ensures that the data remains accurate, consistent, and aligned with the source data.

Materialized views can be refreshed in several ways, depending on the requirements:

  1. Complete Refresh: Recomputes the entire query result and overwrites the materialized view data.
  2. Fast Refresh: Only updates changes (inserts, updates, or deletes) since the last refresh.
  3. Force Refresh: Decides between a fast or complete refresh based on database optimization.
  4. Manual and Automatic Refresh: Determines whether the refresh operation is triggered manually or scheduled automatically.

Syntax for Refreshing Materialized Views

General Syntax

The syntax to refresh a materialized view varies by SQL dialect. Here is a typical syntax for Oracle SQL:

				
					BEGIN
   DBMS_MVIEW.REFRESH('materialized_view_name', 'REFRESH_MODE');
END;

Where:

  • materialized_view_name: The name of the materialized view.
  • REFRESH_MODE: Options like 'COMPLETE', 'FAST', or 'FORCE'.

Types of Refresh Methods

Complete Refresh

This method recomputes the entire query and replaces the contents of the materialized view. It is often slower but ensures consistency.

Example

Consider a materialized view mv_sales_data created as follows:

				
					CREATE MATERIALIZED VIEW mv_sales_data
BUILD IMMEDIATE
REFRESH COMPLETE
AS
SELECT product_id, SUM(sales) AS total_sales
FROM sales
GROUP BY product_id;

To refresh this view:

				
					BEGIN
   DBMS_MVIEW.REFRESH('mv_sales_data', 'COMPLETE');
END;

Output

  • The materialized view is completely rebuilt.
  • All rows are replaced with the latest data from the sales table.

Fast Refresh

Fast refresh updates only the changed rows using a materialized view log, which tracks changes to the base table.

Prerequisite

A materialized view log must be created on the base table:

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

Example

Materialized view mv_sales_data is created to allow fast refresh:

				
					CREATE MATERIALIZED VIEW mv_sales_data
BUILD IMMEDIATE
REFRESH FAST
AS
SELECT product_id, SUM(sales) AS total_sales
FROM sales
GROUP BY product_id;

To refresh:

				
					BEGIN
   DBMS_MVIEW.REFRESH('mv_sales_data', 'FAST');
END;

Output

  • Only incremental changes are applied.
  • Faster than complete refresh but requires a materialized view log.

Force Refresh

This mode automatically decides whether to use a fast or complete refresh, depending on the availability of a materialized view log.

Example

				
					BEGIN
   DBMS_MVIEW.REFRESH('mv_sales_data', 'FORCE');
END;

Output

  • The database optimizes the refresh method, defaulting to fast if possible; otherwise, it falls back to complete refresh.

Automatic Refreshing of Materialized Views

Scheduled Refresh

To automate the refresh, define a refresh interval during view creation.

				
					CREATE MATERIALIZED VIEW mv_sales_data
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
AS
SELECT product_id, SUM(sales) AS total_sales
FROM sales
GROUP BY product_id;

Explanation

  • START WITH: Defines when the refresh starts.
  • NEXT: Defines the interval between refreshes (e.g., every day with SYSDATE + 1).

Triggers for Refresh

You can also use triggers to refresh materialized views after specific events:

				
					CREATE OR REPLACE TRIGGER trg_refresh_sales
AFTER INSERT OR UPDATE OR DELETE ON sales
BEGIN
   DBMS_MVIEW.REFRESH('mv_sales_data', 'FAST');
END;

Performance Optimization Tips for Refreshing Materialized Views

  1. Use Fast Refresh Wherever Possible: Reduces computation time by updating only the changes.
  2. Enable Parallel Refresh: For large datasets, parallel processing can speed up refresh operations.
  3. Refresh During Off-Peak Hours: Schedule refreshes to minimize the impact on system performance.
  4. Filter Data in Materialized Views: Reduce the volume of data stored in the materialized view for faster refresh.

Advanced Concepts

Refresh Groups

You can group multiple materialized views into a refresh group to synchronize their refreshes.

				
					BEGIN
   DBMS_REFRESH.MAKE(
      name     => 'sales_group',
      list     => 'mv_sales_data, mv_product_data',
      next_date => SYSDATE,
      interval  => 'SYSDATE + 1'
   );
END;

On-Demand Refresh

Refresh only when explicitly requested:

				
					CREATE MATERIALIZED VIEW mv_sales_data
REFRESH ON DEMAND
AS
SELECT product_id, SUM(sales) AS total_sales
FROM sales
GROUP BY product_id;

To refresh:

				
					BEGIN
   DBMS_MVIEW.REFRESH('mv_sales_data');
END;

Example of Refresh with Output

Scenario

Base sales table

Product_IDSales
1100
2200

Materialized view mv_sales_data contains:

Product_IDTotal_Sales
1100
2200

An update is made:

				
					UPDATE sales SET sales = 300 WHERE product_id = 1;
COMMIT;

Fast Refresh

				
					BEGIN
   DBMS_MVIEW.REFRESH('mv_sales_data', 'FAST');
END;

Updated Materialized View:

Product_IDTotal_Sales
1300
2200

Refreshing materialized views is a critical task for maintaining data accuracy and performance in SQL databases. Understanding when and how to use each method ensures you can leverage materialized views effectively for your data needs. Happy Coding!❤️

Table of Contents

Home
Roadmap
ATS Resume
Playground
Resources