Materialized View Best Practices FAQS

1. What are Materialized Views in Oracle?

A Materialized View (MV) is a physically stored result of a query that can be refreshed periodically or automatically.

It is mainly used for:

·        Reporting and analytics

·        Data warehousing

·        Query performance optimization

·        Pre-aggregated summary data

Unlike normal views, MVs store data physically and improve query performance.

2. When should you use a Materialized View?

Use MVs when:

·        Queries involve complex joins and aggregations

·        Base tables are very large

·        Reports run frequently with similar logic

·        You need precomputed summaries

·        Query response time must be reduced

Avoid MVs when:

·        Data must always be real-time

·        Base tables change extremely frequently

·        Simpler indexing can solve the problem

3. What refresh method should I choose?

Choosing the correct refresh strategy is critical.

FAST Refresh (Recommended)

·        Best for large tables

·        Uses MV logs

·        Applies incremental changes

·        Ideal for data warehouse systems

COMPLETE Refresh

·        Rebuilds entire MV

·        Works for any query

·        Suitable for small tables or complex queries

FORCE Refresh

·        Attempts FAST, falls back to COMPLETE

·        Good for mixed environments

ON COMMIT

·        Automatic refresh on commit

·        Use only for small tables and real-time needs

ON DEMAND (Best for large systems)

·        Manual or scheduled refresh

·        Reduces transaction overhead

Best practice:
Use FAST + ON DEMAND for large reporting systems.

4. Should I always use FAST refresh?

No.

FAST refresh requires:

·        Materialized View logs on all base tables

·        Query compatibility

If FAST is not possible:

·        Simplify the query

·        Or use COMPLETE refresh during off-peak hours

5. Best practices for MV logs

·        Create logs only on required tables

·        Include only necessary columns

·        Avoid unnecessary logging columns

·        Monitor log growth

·        Remove unused logs

Example:

CREATE MATERIALIZED VIEW LOG ON sales

WITH ROWID, SEQUENCE (region_id, amount)

INCLUDING NEW VALUES;

6. How should I design Materialized Views?

Keep the query simple

Avoid:

·        Complex subqueries

·        Excessive DISTINCT

·        Unnecessary joins

Pre-aggregate data

Store SUM, COUNT, and other aggregates inside the MV.

Use appropriate filtering

Reduce dataset size using WHERE clause.

7. Should I index Materialized Views?

Yes, but carefully.

Index:

·        Join columns

·        Frequently filtered columns

·        Group-by columns

Avoid:

·        Too many indexes

·        Redundant indexes

Balance query performance versus refresh cost.

8. Should I partition Materialized Views?

For large data warehouse systems: Yes.

Benefits:

·        Faster refresh

·        Partition Change Tracking (PCT)

·        Easier maintenance

·        Improved performance

Partition base tables first, then create partitioned MVs.

9. When should I use ON COMMIT refresh?

Use ON COMMIT only when:

·        Data must be real-time

·        Base tables are small or moderate

·        DML volume is manageable

Avoid ON COMMIT for:

·        High-volume transactional systems

·        Large fact tables

It increases commit time.

10. How often should I refresh MVs?

Depends on business needs:

·        Real-time dashboards → ON COMMIT

·        Hourly reports → Scheduled refresh

·        Daily batch reports → Nightly refresh

·        Large warehouse → End-of-day refresh

Always schedule refresh during low activity.

11. How to improve refresh performance?

·        Use FAST refresh

·        Use partitioning with PCT

·        Index base tables

·        Minimize indexes on MV

·        Schedule during off-peak hours

·        Monitor refresh duration

12. How to enable query rewrite?

Query rewrite allows Oracle optimizer to automatically use MV.

ALTER MATERIALIZED VIEW mv_sales_summary ENABLE QUERY REWRITE;

Best practice:

·        Enable QUERY REWRITE for reporting MVs

·        Ensure QUERY_REWRITE_ENABLED = TRUE

13. Common mistakes

·        Using COMPLETE refresh on huge tables

·        Not creating MV logs for FAST refresh

·        Refreshing too frequently

·        Using ON COMMIT for large transactional systems

·        Creating unnecessary indexes

·        Ignoring partitioning

·        Not monitoring refresh time

14. How to monitor Materialized Views?

Check refresh status:

SELECT mview_name, last_refresh_type, last_refresh_date

FROM user_mviews;

Monitor:

·        Refresh duration

·        CPU and I/O usage

·        MV log growth

·        Locking issues

Use AWR/ASH reports in production.

15. Ideal production setup for large systems

For large data warehouse systems:

·        Partitioned base tables

·        Partitioned materialized views

·        FAST refresh with MV logs

·        ON DEMAND scheduled refresh

·        Limited indexing

·        Query rewrite enabled

·        Monitoring refresh duration

16. Interview Tip

“Best practices include choosing the right refresh method—preferably FAST refresh with MV logs—using ON DEMAND scheduling for large systems, indexing wisely, partitioning large tables, enabling query rewrite, and monitoring refresh performance. Avoid ON COMMIT for high DML workloads and avoid COMPLETE refresh for very large tables.”