Materialized Views FAQS

1. What is a Materialized View in Oracle?

A Materialized View (MV) is a database object that:

·        Stores the result of a query physically

·        Contains actual data (unlike a normal view)

·        Can be refreshed periodically

·        Improves performance for complex queries

Commonly used in:

·        Data warehousing

·        Reporting systems

·        Distributed databases

·        Query performance optimization

2. How is a Materialized View different from a normal View?

Feature	View	Materialized View
Stores data physically	No	Yes
Always shows current data	Yes	No (depends on refresh)
Improves query performance	Limited	Yes
Requires storage	No	Yes
Needs refresh	No	Yes

3. Basic Syntax to Create a Materialized View

CREATE MATERIALIZED VIEW mv_emp_summary

BUILD IMMEDIATE

REFRESH COMPLETE

ON DEMAND

AS

SELECT department_id, COUNT(*) emp_count

FROM employees

GROUP BY department_id;

·        BUILD IMMEDIATE → Creates and populates MV immediately

·        REFRESH COMPLETE → Fully rebuilds during refresh

·        ON DEMAND → Refresh only when manually triggered

4. What are the types of Materialized Views?

1. Simple MV

Based on a single table without joins or aggregates.

2. Complex MV

Contains joins, aggregations, GROUP BY, subqueries.

3. Updatable MV

Allows DML operations and propagates changes back to master table (used in replication).

5. What are the refresh modes?

COMPLETE Refresh

·        Re-executes entire query

·        Deletes and reinserts all data

FAST Refresh

·        Updates only changed data

·        Requires MV logs

FORCE Refresh

·        Tries FAST refresh

·        Falls back to COMPLETE if not possible

6. What is a Materialized View Log?

Tracks changes on the base table. Required for FAST refresh.

CREATE MATERIALIZED VIEW LOG ON employees

WITH PRIMARY KEY INCLUDING NEW VALUES;

Records INSERT, UPDATE, DELETE changes.

7. What are refresh timing options?

ON COMMIT

·        Refreshes automatically after COMMIT

·        Good for small tables

ON DEMAND

·        Manual refresh using:

EXEC DBMS_MVIEW.REFRESH('mv_emp_summary');

Better for reporting environments.

8. What is Query Rewrite?

Allows Oracle to automatically use an MV instead of base tables without changing application SQL.

ENABLE QUERY REWRITE

ALTER SESSION SET query_rewrite_enabled = TRUE;

9. How do Materialized Views improve performance?

·        Precompute complex joins and aggregations

·        Reduce CPU usage and disk I/O

·        Avoid repeated heavy calculations

Especially for large reporting queries and data warehouse aggregations.

10. What are common problems with Materialized Views?

·        Fast refresh not working (missing MV logs)

·        Stale data due to infrequent refresh

·        Large complete refresh taking too long

·        Storage growth

·        Locking issues with ON COMMIT refresh

11. How to check if MV is stale?

SELECT mview_name, staleness

FROM user_mviews;

Values: FRESH, STALE, NEEDS_COMPILE

12. Restrictions for FAST refresh

·        MV log on base tables

·        Primary key or ROWID support

·        No unsupported constructs (e.g., some complex outer joins)

13. ON COMMIT vs ON DEMAND

Feature	ON COMMIT	ON DEMAND
Refresh timing	After every commit	Manual/scheduled
OLTP impact	High	Low
Reporting use	Not ideal	Recommended
Data freshness	Immediate	Based on schedule

14. When should you use Materialized Views?

Use when:

·        Heavy aggregation queries are frequent

·        Reporting systems

·        Remote data replication

·        Reduce load on base tables

Avoid when:

·        Real-time accuracy is critical

·        Data changes very frequently

15. Can you index a Materialized View?

Yes.

CREATE INDEX idx_mv_dept ON mv_emp_summary(department_id);

Indexes improve query performance and join efficiency.

16. Internal behavior during refresh

·        Oracle checks MV logs (FAST)

·        Applies incremental changes or rebuilds MV (COMPLETE)

·        Updates metadata in data dictionary

Redo and undo are generated during refresh.

17. How does MV affect storage?

·        Occupies physical storage

·        Can grow large depending on query

·        Needs proper tablespace planning

18. Real-World Scenario

Scenario: Reporting dashboard runs complex joins across large tables.

Solution: Create MV with precomputed summary, refresh nightly ON DEMAND, enable query rewrite.

Result: Reporting becomes faster and database load reduces.

19. Best Practices

·        Use FAST refresh whenever possible

·        Create MV logs properly

·        Schedule refresh during low-traffic hours

·        Avoid ON COMMIT for heavy OLTP tables

·        Monitor staleness regularly

·        Index MVs when needed

·        Test refresh time before production deployment