Mastering Oracle Materialized Views: A Comprehensive Guide

Oracle Materialized View (MVs) are powerful database objects that store the results of a query physically, unlike regular views, which compute data on demand. Widely used in data warehousing and reporting environments, MVs significantly enhance performance by providing pre-computed and periodically refreshed datasets.

From Oracle 11g through 12c and into 19c, materialized views have evolved with enhancements in refresh strategies and indexing options. They support various refresh mechanisms—complete, fast, and force—tailored to different performance and business needs.

In this guide, we’ll explore Oracle Materialized Views in depth, offering syntax, real-world use cases, version-specific tips, and best practices to help you harness their full potential.

Understanding Materialized Views

What is a Materialized View?

A materialized view is a pre-computed, stored snapshot of a query’s result set. Unlike standard views (virtual and always calculated at runtime), MVs enhance query performance by reducing on-the-fly computation, especially for complex joins or aggregations.

Types of Materialized Views in Oracle

• Complete Refresh: Rebuilds the entire view. Best for small or infrequently changed data.
• Fast Refresh: Uses a materialized view log to incrementally apply changes. Ideal for near-real-time updates.
• Force Refresh: Attempts a fast refresh first, and if not possible, defaults to complete refresh.

Creating Materialized Views

Basic Syntax

Creates a materialized view named EMP_SNAPSHOT_MV, which is populated immediately, — refreshed using fast refresh on commit.

CREATE MATERIALIZED VIEW EMP_SNAPSHOT_MV (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
SEGMENT CREATION IMMEDIATE
BUILD IMMEDIATE  USING INDEX 
REFRESH FAST ON COMMIT
AS 
SELECT E.EMPNO EMPNO,
       E.ENAME ENAME,
       E.JOB JOB,
       E.MGR MGR,
       E.HIREDATE HIREDATE,
       E.SAL SAL,
       E.COMM COMM,
       E.DEPTNO DEPTNO 
  FROM EMP E;

Materialized View Log

Essential for fast refresh, this log tracks changes in the base table.

-- Creating a materialized view log on the EMP_SNAPSHOT_MV table, tracking primary key,

-- rowid, and sequence information. Including new values is essential for

-- fast refresh to know the before and after states of the changed rows.

CREATE MATERIALIZED VIEW LOG ON EMP_SNAPSHOT_MV 
WITH PRIMARY KEY, ROWID, SEQUENCE (deptno, mgr)
INCLUDING NEW VALUES;

Version-Specific Syntax Highlights

• Oracle 11g: Basic support for refresh methods and logs.
• Oracle 12c: ON STATEMENT refresh for real-time MVs (limited scenarios).
• Oracle 19c: Enhanced materialized view support with improved optimizer hints.

Specific Options

• BUILD IMMEDIATE vs. BUILD DEFERRED: Determines when the view is populated. IMMEDIATE populates the view during creation, while DEFERRED populates it on the first refresh.
• WITH PRIMARY KEY: This clause is crucial for enabling fast refresh. The base table of the materialized view must have a defined and enabled primary key constraint for Oracle to efficiently identify and apply changes based on the materialized view log.
• ON PREBUILT TABLE: Uses an existing table structure for the MV. The table structure must exactly match the SELECT statement of the materialized view.

Let’s creates a materialized view named mv_orders using a pre-existing table, refreshing it using the fast refresh method, and assumes a primary key exists on the base table.

CREATE MATERIALIZED VIEW orders_mv
ON PREBUILT TABLE
WITH PRIMARY KEY
REFRESH FAST
AS
SELECT * FROM orders;

Indexing Oracle Materialized View

Creates an index named idx_mv_sales_region on the region column of the mv_sales_summary materialized view to improve query performance on that column.

CREATE INDEX idx_mv_sales_region ON mv_sales_summary (region);

Restrictions

• Subqueries in SELECT or WHERE clauses can sometimes be disallowed, especially for certain refresh methods or complex subqueries.
• Materialized views using database links for their base tables can have limitations on refresh methods. Often, complete refresh (REFRESH COMPLETE) is the most reliable approach. Ensure the database link is properly configured and the necessary permissions are granted to the user owning the materialized view on the remote database.

Maintaining Oracle Materialized View

Refreshing Oracle Materialized View

Let’s perform a fast refresh of the mv_sales_summary materialized view, applying only the changes since the last refresh.

Also perform a complete refresh of the mv_sales_summary materialized view, rebuilding it entirely.

-- Fast refresh
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', 'F');

-- Complete refresh
EXEC DBMS_MVIEW.REFRESH('mv_sales_summary', 'C');

Automatic Refresh Scheduling

Now let’s create a materialized view named mv_inventory that is refreshed automatically every hour.

CREATE MATERIALIZED VIEW mv_inventory
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + (1/24) -- Refreshes every hour
AS
SELECT *
  FROM inventory;

Use refresh groups for batch updates

Also create a refresh group named my_group that includes mv_sales_summary and mv_inventory, scheduled to refresh every 30 minutes.

BEGIN
--Refreshes every 30 minutes (1 day / 48 intervals)
   DBMS_REFRESH.MAKE (
      name => 'my_group',
      list => 'mv_sales_summary, mv_inventory',
      next_date => SYSDATE,
      interval => 'SYSDATE + 1/48' 
      );
END;

Manual Refresh

In the below example we manually perform a complete refresh of the mv_orders materialized view.

EXEC DBMS_MVIEW.REFRESH('mv_orders', 'C');

Incremental Refresh Example

Let’s create a materialized view log on the orders table to enable fast refreshes of materialized views based on this table. Ensure a materialized view log exists:

CREATE MATERIALIZED VIEW LOG ON orders;

Then:

Now create a materialized view named mv_order_summary, which is refreshed on demand using fast refresh, counting the number of orders per customer.

CREATE MATERIALIZED VIEW mv_order_summary
REFRESH FAST ON DEMAND
AS
SELECT customer_id, COUNT(*)
  FROM orders
GROUP BY customer_id;

Monitoring Refresh Times

You can also retrieve information about materialized views, including their names, last refresh dates, next refresh dates, last refresh types, and staleness status.

SELECT mview_name,
       last_refresh_date,
       next_date,
       last_refresh_type,
       staleness
  FROM USER_MVIEWS;

Compiling Materialized Views

You need to recompile the mv_sales_summary materialized view to ensure it is valid after changes to the underlying tables. This action needed after any base table modifications.

ALTER MATERIALIZED VIEW mv_sales_summary COMPILE;

Truncating a Materialized View

You can use “TRUNCATE TABLE” to remove all rows from the mv_sales_summary materialized view.

-- Rebuild needed after truncate (either fully refresh or rebuild)
TRUNCATE TABLE mv_sales_summary;

Managing and Monitoring Materialized Views

Oracle provides several data dictionary views to help manage and monitor materialized views. These views offer insights into the definitions, refresh schedules, and log details of materialized views, enabling database administrators and developers to effectively track and maintain these objects. Key data dictionary views include USER_MVIEWS / ALL_MVIEWS / DBA_MVIEWS for viewing materialized view definitions,USER_MVIEW_LOGS for information about materialized view logs, and DBA_REFRESH and DBA_REFRESH_CHILDREN for details on refresh scheduling.

Key Data Dictionary Views

• USER_MVIEWS / ALL_MVIEWS / DBA_MVIEWS – View definitions.
• USER_MVIEW_LOGS – Log details.
• DBA_REFRESH / DBA_REFRESH_CHILDREN – Refresh scheduling.

Useful Queries

Below query retrieves the names, refresh methods, and build modes of materialized views owned by the current user.

SELECT mview_name,
       refresh_method,
       build_mode
  FROM user_mviews;
SELECT log_table
  FROM user_mview_logs;

Dropping Materialized Views and Logs

Removes the materialized view or view log associated with the specified base table, which is no longer needed if you are not using fast refreshes on that table.

Drop MV

DROP MATERIALIZED VIEW mv_order_summary;

Drop MV Log

DROP MATERIALIZED VIEW LOG ON orders;

Conclusion

Materialized views are indispensable for performance tuning, snapshot reporting, and efficient data replication in Oracle environments. Whether you’re optimizing heavy joins, building fast dashboards, or implementing ETL strategies, MVs can transform your database’s responsiveness.

Understanding the syntax, refresh options, and monitoring mechanisms gives you a strong command over this feature. Continue experimenting with real-world datasets and different Oracle versions to deepen your mastery.