Materialized Views in Oracle – A Practical Guide

1. Introduction: What is a Materialized View?

A Materialized View (MV) is a database object that stores the result of a query physically on disk, unlike a normal view which is just a stored SQL definition.

Think of it as:

A cached snapshot of query results that can be refreshed periodically.

Why use Materialized Views?

They are mainly used for:

• Performance optimization (especially for heavy joins and aggregations)
• Reporting systems / dashboards
• Pre-computed aggregations (data warehouse use cases)
• Reducing load on OLTP tables

View vs Materialized View

View	Materialized View
Stores only SQL	Stores actual data
Always shows real-time data	Data may be stale
No refresh needed	Needs refresh
Slower for complex queries	Much faster for reads

---

2. Prerequisites to Create a Materialized View

Before creating an MV, ensure:

Required Privileges

You need:

GRANT CREATE MATERIALIZED VIEW TO username;
GRANT QUERY REWRITE TO username;

If using tables from another schema:

GRANT SELECT ON schema.table TO username;

For FAST refresh (important)

If you want FAST refresh, you must create a Materialized View Log on base tables.

Example:

CREATE MATERIALIZED VIEW LOG ON employees
WITH PRIMARY KEY, ROWID, SEQUENCE
INCLUDING NEW VALUES;

For Fast Refresh, having a Materialised View Log is Mandatory, And needs to follow the rules listed in Oracle Database Data Warehousing Guide

---

3. How to Create a Materialized View

Basic Syntax

CREATE MATERIALIZED VIEW <view-name>
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

---

Important Clauses Explained

1. BUILD IMMEDIATE vs BUILD DEFERRED

Option	Meaning
BUILD IMMEDIATE	Data populated immediately
BUILD DEFERRED	Created empty; populated on first refresh

---

2. REFRESH Methods

Method	Description
COMPLETE	Re-runs full query (slow)
FAST	Uses materialized view logs (fast, incremental)
FORCE	Tries FAST, falls back to COMPLETE if FAST is not possible or fails

---

3. Refresh Timing

Option	Meaning
ON COMMIT	Refreshes automatically after each commit
ON DEMAND	Refresh only when manually triggered
START WITH / NEXT	Scheduled refresh

Example scheduled refresh:

REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1/24 --(Refresh every hour)

---

Example with FAST Refresh

Step 1: Create MV log

CREATE MATERIALIZED VIEW LOG ON orders
WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES;

Step 2: Create MV

CREATE MATERIALIZED VIEW mv_order_summary
REFRESH FAST
ON DEMAND
AS
SELECT customer_id, SUM(amount) total_amount
FROM orders
GROUP BY customer_id;

---

4. How to Drop a Materialized View

Simple:

DROP MATERIALIZED VIEW mv_order_summary;

If you also want to remove logs:

DROP MATERIALIZED VIEW LOG ON orders;

---

5. How to Refresh Materialized Views

There are three main approaches.

---

Method 1: Using Built-in Refresh (START WITH/ NEXT clause)

This is the scheduled refresh method.

Example:

CREATE MATERIALIZED VIEW mv_sales
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
AS
SELECT * FROM sales;

This refreshes once every day automatically.

---

Method 2: Using DBMS_MVIEW / DBMS_REFRESH Package

Refresh a single MV

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

Where:

• 'C' = COMPLETE
• 'F' = FAST
• '?' = FORCE

Example:

EXEC DBMS_MVIEW.REFRESH('MV_SALES', 'F');

---

Refresh Multiple MVs Together (Refresh Group)

This is where DBMS_REFRESH is useful.

Create a refresh group:

BEGIN
  DBMS_REFRESH.MAKE(
    name => 'my_refresh_group',
    list => 'mv_sales, mv_customers',
    next_date => SYSDATE,
    interval => 'SYSDATE + 1/24'
  );
END;
/

Manually refresh group:

EXEC DBMS_REFRESH.REFRESH('my_refresh_group');

Method 3: Using DBMS_SCHEDULER Package

Oracle nowadays has started recommending DBMS_SCHEDULER over the inbuilt Start With/ Next type approach for scheduling refresh

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'refresh_mv_sales_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_MVIEW.REFRESH(''MV_SALES_SUMMARY'', ''C''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY',
    enabled         => TRUE
  );
END;
/

References

https://oracle-base.com/articles/misc/materialized-views

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-MATERIALIZED-VIEW.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DROP-MATERIALIZED-VIEW.html