Oracle Materialized View FAQs with Examples

An Oracle materialized view (MV) is a database object that stores the result of a query physically.

Unlike a normal view, which runs its query when you access it, a materialized view stores the query result and can later be refreshed to reflect changes in the underlying tables.

The basic idea is:

Base Tables
    ↓
Query
    ↓
Materialized View
    ↓
Stored Result

This makes materialized views especially useful for:

  • Reporting
  • Data warehousing
  • Aggregations
  • Summary tables
  • Performance optimization
  • Reducing expensive joins and calculations

1. What is a materialized view?

A materialized view stores the result of a query.

Example:

CREATE MATERIALIZED VIEW mv_emp_summary
AS
SELECT department_id,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Unlike a normal view:

CREATE VIEW emp_summary AS
SELECT ...

the materialized view actually stores the resulting data.

For example:

DEPARTMENT_ID EMPLOYEE_COUNT TOTAL_SALARY
10 5 250000
20 8 420000
30 4 180000

2. What is the difference between a view and a materialized view?

This is one of the most common interview questions.

Feature View Materialized View
Stores query result ❌ ✅
Physically stores data ❌ ✅
Query executed when accessed Usually Not necessarily
Needs refresh ❌ Usually
Faster for expensive queries Not necessarily Often
Can contain aggregations ✅ ✅
Uses storage Minimal ✅
Suitable for data warehouse Sometimes ✅

Normal view

SELECT
    ↓
Base tables
    ↓
Result

Materialized view

Base tables
    ↓
SELECT
    ↓
Stored result
    ↓
SELECT from MV

3. Why do we use materialized views?

Suppose you have a huge sales table:

CREATE TABLE sales (
    sale_id       NUMBER,
    customer_id   NUMBER,
    product_id    NUMBER,
    sale_date     DATE,
    quantity      NUMBER,
    amount        NUMBER
);

Every day, a report runs:

SELECT product_id,
       SUM(quantity),
       SUM(amount)
FROM sales
GROUP BY product_id;

If sales contains 500 million rows, this aggregation can be expensive.

Instead:

CREATE MATERIALIZED VIEW mv_product_sales
AS
SELECT product_id,
       SUM(quantity) AS total_quantity,
       SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

Now the reporting query can read the precomputed results.

500 million sales rows
        ↓
Materialized View
        ↓
Small summary
        ↓
Fast reporting

4. Does a materialized view contain physical data?

Yes.

This is a major difference from a normal view.

A normal view stores:

SQL definition

A materialized view stores:

SQL definition
+
physical result data

So materialized views require storage.

5. What happens when base-table data changes?

The materialized view does not necessarily change immediately.

Suppose:

INSERT INTO sales
VALUES (
    101,
    10,
    1001,
    SYSDATE,
    5,
    500
);

The materialized view may still contain its old result until it is refreshed.

Base table changes
        ↓
Materialized view
        ↓
Still has old result
        ↓
REFRESH
        ↓
New result

6. How do I manually refresh a materialized view?

Use:

EXEC DBMS_MVIEW.REFRESH('MV_PRODUCT_SALES');

For example:

BEGIN
    DBMS_MVIEW.REFRESH('MV_PRODUCT_SALES');
END;
/

After the refresh, the materialized view reflects the underlying data according to its refresh mechanism.

7. What is REFRESH COMPLETE?

A complete refresh rebuilds the materialized view result from the underlying query.

Example:

BEGIN
    DBMS_MVIEW.REFRESH(
        'MV_PRODUCT_SALES',
        METHOD => 'C'
    );
END;
/

Conceptually:

Existing MV data
        ↓
Discard/rebuild
        ↓
Execute complete query
        ↓
New MV data

Complete refresh can be expensive for very large materialized views.

8. What is a FAST refresh?

A fast refresh updates the materialized view using changes recorded from the base tables instead of rebuilding the entire result.

Conceptually:

Base table
    ↓
Changes recorded
    ↓
Materialized view
    ↓
Apply only changes

For example:

BEGIN
    DBMS_MVIEW.REFRESH(
        'MV_PRODUCT_SALES',
        METHOD => 'F'
    );
END;
/

However, not every materialized view is eligible for fast refresh.

This is an important interview point.

9. What is FORCE refresh?

FORCE tells Oracle to use a fast refresh if possible; otherwise it can perform a complete refresh.

Conceptually:

FORCE
 |
 +-- Fast refresh possible?
 |       |
 |       +-- YES → FAST
 |
 +-- NO → COMPLETE

Example:

BEGIN
    DBMS_MVIEW.REFRESH(
        'MV_PRODUCT_SALES',
        METHOD => '?'
    );
END;
/

In practical code, use the documented refresh method options supported by your Oracle version, such as F, C, or ? through DBMS_MVIEW.REFRESH.

10. What is a materialized view log?

A materialized view log records changes made to a base table so Oracle can potentially perform a fast refresh.

Example:

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

Then:

CREATE MATERIALIZED VIEW mv_product_sales
REFRESH FAST
AS
SELECT product_id,
       SUM(quantity) AS total_quantity,
       SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

Conceptually:

SALES
 |
 +----> Materialized View Log
 |              |
 |              ↓
 |        Changes recorded
 |
 +----> Materialized View
 |
 ↓
FAST REFRESH

11. Why do we need a materialized view log?

Suppose:

SALES = 100 million rows
Only 100 rows changed.

Without change information, Oracle may need to process a large amount of data to determine the new result.

A materialized view log can record the relevant changes:

100 million existing rows
+
100 changed rows
        ↓
Materialized View Log
        ↓
Fast refresh

This can make incremental refresh much more efficient when the materialized view meets the fast-refresh requirements.

12. How do I create a materialized view with automatic refresh?

You can specify refresh behavior when creating the materialized view.

Example:

CREATE MATERIALIZED VIEW mv_product_sales
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT product_id,
       SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

Here:

BUILD IMMEDIATE
        ↓
Build MV when created

REFRESH COMPLETE
        ↓
Complete refresh

ON DEMAND
        ↓
Refresh when explicitly requested

13. What is ON DEMAND?

ON DEMAND means Oracle doesn't automatically refresh the materialized view simply because the base table changes.

You explicitly refresh it:

BEGIN
    DBMS_MVIEW.REFRESH('MV_PRODUCT_SALES');
END;
/

This is useful for reporting systems where data can be refreshed periodically.

14. What is ON COMMIT?

ON COMMIT means the materialized view is refreshed when the relevant transaction commits, subject to the materialized view's refresh eligibility and restrictions.

Example:

CREATE MATERIALIZED VIEW mv_emp_summary
REFRESH FAST
ON COMMIT
AS
SELECT department_id,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

Conceptually:

INSERT/UPDATE/DELETE
        ↓
Transaction
        ↓
COMMIT
        ↓
Materialized view refresh

Important:

ON COMMIT can add overhead to DML because maintaining the materialized view becomes part of transaction processing.

15. What is BUILD IMMEDIATE?

BUILD IMMEDIATE means Oracle populates the materialized view when it is created.

Example:

CREATE MATERIALIZED VIEW mv_emp
BUILD IMMEDIATE
AS
SELECT *
FROM employees;

Conceptually:

CREATE MV
    ↓
Execute query
    ↓
Store result immediately

16. What is BUILD DEFERRED?

BUILD DEFERRED creates the materialized view definition without immediately populating the result data.

Example:

CREATE MATERIALIZED VIEW mv_emp
BUILD DEFERRED
AS
SELECT *
FROM employees;

The initial population occurs later according to the applicable refresh/build process.

17. What is the difference between ON COMMIT and ON DEMAND?

Feature ON COMMIT ON DEMAND
Refresh timing At commit Explicit/scheduled
Automatic ✅ ❌
DML overhead Higher Usually lower
Good for Near-current data Reporting/batch
User controls refresh ❌ ✅

Remember:

ON COMMIT
    ↓
Commit causes refresh

ON DEMAND
    ↓
You decide when to refresh

18. Can a materialized view be refreshed periodically?

Yes.

You can use a scheduler job or other supported mechanisms to refresh it periodically.

For example, a scheduler job could execute:

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

The concept is:

Every hour
    ↓
Scheduler
    ↓
DBMS_MVIEW.REFRESH
    ↓
Materialized View updated

This is common in reporting environments.

19. What is query rewrite?

Query rewrite allows Oracle's optimizer to use a materialized view to answer a query, even when the query doesn't explicitly reference the materialized view.

Suppose:

CREATE MATERIALIZED VIEW mv_product_sales
AS
SELECT product_id,
       SUM(amount) total_amount
FROM sales
GROUP BY product_id;

A user runs:

SELECT product_id,
       SUM(amount)
FROM sales
GROUP BY product_id;

Oracle may recognize that:

User query
    ↓
Can be answered using MV
    ↓
MV_PRODUCT_SALES

instead of scanning the base table.

This can dramatically improve reporting performance.

20. How do I enable query rewrite?

For example:

CREATE MATERIALIZED VIEW mv_product_sales
ENABLE QUERY REWRITE
AS
SELECT product_id,
       SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

The optimizer can then consider the MV for eligible queries.

You can also inspect whether query rewrite is being used through execution plans.

21. Does query rewrite always use the materialized view?

No.

Oracle's optimizer decides whether an available materialized view can and should be used.

Factors include:

  • Query equivalence
  • Materialized view definition
  • Freshness/staleness settings
  • Optimizer decisions
  • Query rewrite eligibility

So:

ENABLE QUERY REWRITE
≠
Oracle must always use MV

It means Oracle may consider it.

22. What is a stale materialized view?

A materialized view is stale when its stored data no longer reflects the current state of its base tables.

Example:

10:00
    ↓
MV refreshed

10:10
    ↓
Base table updated

10:11
    ↓
MV not refreshed

Therefore:

Base table = current
MV         = old

The MV is stale.

23. How can I check materialized view information?

You can query:

SELECT
    mview_name,
    staleness,
    last_refresh_type,
    last_refresh_date
FROM user_mviews;

Useful columns include:

  • MVIEW_NAME
  • STALENESS
  • LAST_REFRESH_TYPE
  • LAST_REFRESH_DATE

For all accessible materialized views:

SELECT
    owner,
    mview_name,
    staleness
FROM all_mviews;

24. What does STALENESS mean?

It tells you whether the materialized view is considered current relative to its base data.

You may encounter values such as:

  • FRESH
  • STALE
  • UNKNOWN

Conceptually:

FRESH
    ↓
MV reflects applicable base data

STALE
    ↓
Base data changed after MV refresh

UNKNOWN
    ↓
Oracle cannot determine freshness in the usual way

25. Can a materialized view become stale after INSERT?

Yes.

Suppose:

INSERT INTO sales
VALUES (1001, 10, 20, SYSDATE, 5, 500);

If the MV is ON DEMAND and hasn't been refreshed:

SALES
    ↓
New row
    ↓
MV not refreshed
    ↓
MV may become STALE

You can then refresh:

BEGIN
    DBMS_MVIEW.REFRESH('MV_PRODUCT_SALES');
END;
/

26. Can a materialized view be based on multiple tables?

Yes.

Example:

CREATE MATERIALIZED VIEW mv_customer_sales
AS
SELECT
    c.customer_id,
    c.customer_name,
    SUM(s.amount) AS total_sales
FROM customers c
JOIN sales s
ON s.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.customer_name;

This is particularly useful for expensive reporting joins.

27. Can a materialized view contain joins?

Yes.

For example:

CREATE MATERIALIZED VIEW mv_emp_dept
AS
SELECT
    e.employee_id,
    e.employee_name,
    d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

The result is physically stored.

28. Can a materialized view contain aggregate functions?

Yes.

This is one of their most common uses.

Example:

CREATE MATERIALIZED VIEW mv_dept_salary
AS
SELECT
    department_id,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees
GROUP BY department_id;

This can turn an expensive repeated aggregation into a lookup against precomputed results.

29. Can a materialized view have indexes?

Yes.

Because a materialized view stores data, you can index its stored result.

Example:

CREATE INDEX idx_mv_dept
ON mv_dept_salary(department_id);

Conceptually:

Materialized View
    ↓
Physical rows
    ↓
Index
    ↓
Faster access

This is another important difference from a normal view.

30. Can a materialized view have constraints?

Materialized views have some restrictions compared with ordinary tables, but you can use indexes and, depending on the design/use case, constraints can be associated with materialized-view data.

In most interview scenarios, the key point to remember is:

A materialized view stores physical data and can have indexes.

31. Can we partition a materialized view?

Yes, depending on the Oracle version and materialized-view design.

Partitioning can be useful for very large materialized views, especially in data warehouse environments.

For example, a materialized view summarizing sales by date may be designed around date-based partitioning.

Conceptually:

MV_SALES_SUMMARY
|
+-- 2025
+-- 2026
+-- 2027

This can help with maintenance and query performance.

32. What is REFRESH COMPLETE vs REFRESH FAST?

This is a very common interview question.

Feature COMPLETE FAST
Rebuild result ✅ ❌
Uses changes ❌ ✅
Usually more work ✅ Lower when eligible
Requires MV log Not necessarily Often
Suitable for large incremental changes Less ideal Often better
Eligibility restrictions Fewer More

Remember:

COMPLETE
    ↓
Recalculate the MV

FAST
    ↓
Apply recorded changes

33. What is REFRESH FORCE?

FORCE gives Oracle the choice to use fast refresh if possible, otherwise complete refresh.

Conceptually:

FORCE
    ↓
Fast refresh possible?
├── Yes → FAST
└── No  → COMPLETE

This can be useful when you don't want application code to determine the refresh method each time.

34. What is a primary-key materialized view?

A materialized view can be designed around primary-key-based change tracking.

For example:

CREATE MATERIALIZED VIEW LOG ON customers
WITH PRIMARY KEY;

Then a materialized view can be built using the relevant base-table information.

The primary-key approach allows Oracle to identify changed rows based on primary keys rather than relying on physical row identifiers.

35. What is a ROWID materialized view?

A materialized view log can also record ROWID information.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID;

This allows Oracle to use row identifiers when determining changes for applicable fast-refresh scenarios.

Interview comparison:

PRIMARY KEY
    ↓
Tracks rows by primary key

ROWID
    ↓
Tracks rows using physical row identifiers

36. What does INCLUDING NEW VALUES mean?

For materialized view logs, it can cause new values of updated columns to be recorded.

Example:

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID
INCLUDING NEW VALUES;

This can be required for certain fast-refresh scenarios involving updates.

The exact columns/options required depend on the materialized-view query and Oracle's fast-refresh requirements.

37. Can I create a materialized view without a materialized view log?

Yes.

Example:

CREATE MATERIALIZED VIEW mv_emp
REFRESH COMPLETE
ON DEMAND
AS
SELECT department_id,
       COUNT(*)
FROM employees
GROUP BY department_id;

A materialized view log is particularly important for many fast-refresh scenarios, but it is not universally required for every materialized view.

38. Can a materialized view be refreshed automatically?

Yes.

Common approaches include:

  • ON COMMIT
  • Scheduled refreshes using Oracle scheduling facilities

Example:

Every night at 2 AM
        ↓
Scheduler
        ↓
DBMS_MVIEW.REFRESH
        ↓
Materialized view refreshed

For large data warehouses, scheduled refreshes are very common.

39. What is the difference between ON COMMIT and a scheduled refresh?

ON COMMIT

DML
 ↓
COMMIT
 ↓
MV refresh

Scheduled

DML
 ↓
MV remains unchanged
 ↓
Scheduled time
 ↓
MV refresh

Use ON COMMIT when you need more immediate synchronization and can tolerate DML overhead.

Use Scheduled / ON DEMAND when periodic reporting refresh is sufficient.

40. Can we drop a materialized view?

Yes.

Use:

DROP MATERIALIZED VIEW mv_product_sales;

This removes the materialized view.

If you have created a separate materialized view log, that is a separate object and may need separate management.

41. Can we alter a materialized view?

Some materialized-view properties can be altered using:

ALTER MATERIALIZED VIEW

For example, you can modify certain refresh-related or storage-related properties.

For significant query-definition changes, it is often necessary to drop and recreate the materialized view.

42. Can we truncate a materialized view?

Materialized views have special maintenance semantics, so you should not treat them exactly like ordinary tables.

Instead of manually modifying the stored result, use the materialized-view refresh mechanisms:

DBMS_MVIEW.REFRESH(...)

The materialized view's contents are managed by Oracle's MV mechanisms.

43. Can users directly INSERT or UPDATE a materialized view?

Generally, a materialized view should be treated as a derived, system-maintained result, not as a normal application table.

The normal approach is:

Modify base tables
    ↓
Refresh MV
    ↓
MV reflects changes

rather than:

UPDATE materialized_view

44. What is a read-only materialized view?

A materialized view can be defined as read-only in appropriate scenarios.

Example:

CREATE MATERIALIZED VIEW mv_emp
REFRESH COMPLETE
ON DEMAND
AS
SELECT *
FROM employees;

The important distinction is that the materialized view's data is maintained through its refresh process rather than ordinary application DML.

45. What is a materialized view used for in data warehousing?

This is one of the most important real-world applications.

Suppose you have:

FACT_SALES
|
+---- CUSTOMER
|
+---- PRODUCT
|
+---- DATE

A reporting query may require:

JOIN
+
GROUP BY
+
SUM
+
COUNT
+
AVG

over billions of rows.

A materialized view can precompute:

  • Daily Sales
  • Monthly Sales
  • Product Sales
  • Customer Sales
  • Regional Sales

Example:

CREATE MATERIALIZED VIEW mv_monthly_sales
AS
SELECT
    TRUNC(sale_date, 'MM') AS sales_month,
    product_id,
    SUM(amount) AS total_sales
FROM sales
GROUP BY
    TRUNC(sale_date, 'MM'),
    product_id;

Instead of repeatedly scanning the detailed sales table, reports can potentially use the summarized MV.

46. What is the performance advantage of a materialized view?

Suppose:

SALES
= 1 billion rows

Query:

SELECT
    product_id,
    SUM(amount)
FROM sales
GROUP BY product_id;

Without a suitable MV:

1 billion rows
    ↓
Scan
    ↓
GROUP BY
    ↓
Aggregation
    ↓
Result

With an appropriate MV:

1 billion rows
    ↓
Periodic refresh
    ↓
MV contains summary
    ↓
Query summary
    ↓
Much less work

The trade-off is:

Faster queries
+
Storage
+
Refresh cost

47. What is the main disadvantage of materialized views?

The biggest disadvantages are:

48. Storage

The result is physically stored.

49. Refresh overhead

The MV needs to be maintained.

50. Potentially stale data

With ON DEMAND or scheduled refreshes, the result may not be current.

Performance improvement
+
Maintenance cost

48. View vs Materialized View: interview example

View

CREATE VIEW v_emp_summary AS
SELECT department_id,
       SUM(salary) total_salary
FROM employees
GROUP BY department_id;

Query:

SELECT *
FROM v_emp_summary;

Oracle generally executes the underlying query when the view is queried.

Materialized view

CREATE MATERIALIZED VIEW mv_emp_summary
AS
SELECT department_id,
       SUM(salary) total_salary
FROM employees
GROUP BY department_id;

Query:

SELECT *
FROM mv_emp_summary;

Oracle stores the result.

The query reads the materialized result.

49. Materialized View interview traps

Trap 1

Does a materialized view physically store data?

✅ Yes.

Trap 2

Does a normal view physically store its query result?

❌ No.

Trap 3

Does an MV automatically reflect every base-table change?

❌ Not necessarily.

It depends on its refresh configuration.

Trap 4

What is the purpose of an MV log?

To record relevant base-table changes that can support fast refresh.

Trap 5

Does FAST refresh always work?

❌ No.

The materialized view must satisfy Oracle's fast-refresh requirements.

Trap 6

Does ON COMMIT mean the MV is refreshed whenever a row changes?

Not exactly.

It is associated with the commit of the transaction, subject to Oracle's restrictions and refresh eligibility.

Trap 7

Can an MV improve query performance?

✅ Yes, especially for expensive joins and aggregations.

Trap 8

Does ENABLE QUERY REWRITE force Oracle to use the MV?

❌ No.

It makes the MV available for consideration by query rewrite.

50. Complete practical example

Let's build a simple reporting materialized view.

Base table

CREATE TABLE sales (
    sale_id       NUMBER PRIMARY KEY,
    product_id    NUMBER,
    sale_date     DATE,
    quantity      NUMBER,
    amount        NUMBER
);

Insert sample data:

INSERT INTO sales
VALUES (1, 101, DATE '2026-08-01', 5, 500);

INSERT INTO sales
VALUES (2, 101, DATE '2026-08-02', 3, 300);

INSERT INTO sales
VALUES (3, 102, DATE '2026-08-02', 10, 1000);

COMMIT;

Create materialized view

CREATE MATERIALIZED VIEW mv_product_sales
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_amount
FROM sales
GROUP BY product_id;

Now:

SELECT *
FROM mv_product_sales;

could return:

PRODUCT_ID TOTAL_QUANTITY TOTAL_AMOUNT
101 8 800
102 10 1000

Now add another sale:

INSERT INTO sales
VALUES (
    4,
    101,
    DATE '2026-08-03',
    2,
    200
);

COMMIT;

The MV may still contain:

101 → 800

until it is refreshed.

Refresh:

BEGIN
    DBMS_MVIEW.REFRESH('MV_PRODUCT_SALES');
END;
/

Now:

101 → 1000

because:

800 + 200 = 1000

51. Materialized View cheat sheet

MATERIALIZED VIEW
-----------------

Stores result       ✅

Physical data       ✅

Normal view         ❌ Stores only definition

Refresh             ✅ Required/used to maintain result

FAST refresh        Incremental changes when eligible

COMPLETE refresh    Rebuild result

ON COMMIT           Refresh around transaction commit

ON DEMAND           Refresh explicitly/scheduled

MV LOG              Records base-table changes

Query Rewrite       Allows optimizer to consider MV

Indexes             ✅

Good for            Reporting/Data Warehouse

Main advantage      Faster expensive queries

Main disadvantage   Refresh + storage cost

52. The easiest way to remember materialized views

Think of a normal view as:

"Run this query whenever I ask."

A materialized view as:

"Run this query now,
store the result,
and refresh it later."

Most important interview formula

MATERIALIZED VIEW
|
+-------------+-------------+
|             |             |
Storage       Refresh       Query Rewrite
|             |             |
Physical data FAST/COMPLETE  Performance
              ON COMMIT
              ON DEMAND

One-line interview answer

An Oracle materialized view is a physically stored result of a query that can be refreshed from its base tables, commonly used to improve the performance of expensive reporting, aggregation, and data-warehouse queries.

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)