Materialized View Performance FAQs

1. What affects Materialized View performance?

Materialized View (MV) performance depends on:

·        Size of base tables

·        Type of refresh (FAST, COMPLETE, FORCE)

·        Frequency of refresh

·        Presence of indexes

·        Use of partitioning

·        Query complexity (joins, aggregates, subqueries)

·        Availability of materialized view logs

Performance issues typically appear during:

·        Refresh operations

·        Query execution on the MV

·        High DML activity on base tables

2. Which refresh method performs best?

Refresh Type	Performance Impact	Best Use Case
FAST	Very fast (incremental)	Large tables with frequent small changes
COMPLETE	Slow for large data	Small tables or complex queries
FORCE	Fast if FAST possible, else slow	Mixed scenarios
ON COMMIT	Adds overhead to every commit	Real-time small summaries
ON DEMAND	No runtime overhead until refresh	Batch reporting

FAST refresh generally offers the best performance for large systems.

3. Why is FAST refresh faster?

FAST refresh:

·        Uses Materialized View logs

·        Applies only incremental changes

·        Avoids full table scans

·        Minimizes I/O and CPU usage

Instead of rebuilding millions of rows, it processes only changed rows.

4. Why is COMPLETE refresh slow?

COMPLETE refresh:

1.    Deletes all rows from MV

2.    Re-executes the entire query

3.    Rebuilds data from scratch

For large tables:

·        Full table scans occur

·        High disk I/O

·        Longer locks on MV

5. How can I improve MV refresh performance?

1. Use FAST refresh whenever possible

Create MV logs:

CREATE MATERIALIZED VIEW LOG ON sales

WITH ROWID, SEQUENCE (region_id, amount)

INCLUDING NEW VALUES;

2. Index base tables properly

Indexes reduce full table scans during refresh.

3. Index the Materialized View

Especially on:

·        Join columns

·        Filter columns

·        Group-by columns

4. Use partitioning

Partitioned tables improve refresh speed, especially with Partition Change Tracking (PCT).

5. Schedule refresh during off-peak hours

Avoid heavy refresh during peak transactional workload.

6. How does indexing affect MV performance?

Indexes improve:

·        Query speed on MV

·        Refresh performance for COMPLETE refresh

However:

·        Too many indexes slow down refresh due to index maintenance

·        Use only necessary indexes

Balance is important.

7. What is Partition Change Tracking (PCT)?

PCT allows:

·        FAST refresh on partitioned tables

·        Refreshing only affected partitions

Benefits:

·        Massive performance improvement for large data warehouses

·        Avoids full MV rebuild

Best for:

·        Date-based partitioned fact tables

8. How does ON COMMIT refresh affect performance?

ON COMMIT:

·        Refresh happens during commit

·        Adds overhead to DML operations

·        Can slow down transactions

Use only when:

·        Real-time data is required

·        Base tables are small to medium size

9. How to monitor MV performance?

Check refresh history:

SELECT mview_name, last_refresh_type, last_refresh_date

FROM user_mviews;

Check refresh duration:

SELECT *

FROM dba_mview_refresh_times;

Monitor:

·        CPU usage

·        I/O wait events

·        Long-running refresh jobs

Use AWR/ASH reports in production environments.

10. Why is FAST refresh falling back to COMPLETE?

Common reasons:

·        Missing MV logs

·        Unsupported query features

·        Structural changes to base table

·        Direct-path inserts without logging

When fallback happens, performance drops significantly.

11. How does query complexity affect MV performance?

Complex queries with:

·        Multiple joins

·        Subqueries

·        DISTINCT

·        Outer joins

May:

·        Prevent FAST refresh

·        Increase COMPLETE refresh time

Simplify MV queries when possible.

12. How to optimize MV for reporting performance?

·        Pre-aggregate heavy calculations

·        Create bitmap indexes for low-cardinality columns

·        Use query rewrite feature

·        Enable QUERY REWRITE:

ALTER MATERIALIZED VIEW mv_sales_summary ENABLE QUERY REWRITE;

This allows Oracle optimizer to automatically use MV instead of base tables.

13. Common performance mistakes

·        Using COMPLETE refresh for large fact tables

·        Creating too many indexes on MV

·        Using ON COMMIT on high-DML tables

·        Refreshing too frequently

·        Ignoring partitioning in data warehouse environments

14. Best practices for high-performance MVs

·        Use FAST refresh with MV logs

·        Partition large base tables

·        Use PCT for large fact tables

·        Index wisely (not excessively)

·        Avoid ON COMMIT for high-DML environments

·        Monitor refresh statistics regularly

·        Use ON DEMAND for large reporting MVs

·        Simplify MV query logic

15. Interview Tip

“Materialized View performance mainly depends on refresh strategy. FAST refresh using MV logs is best for large tables. COMPLETE refresh rebuilds the entire MV and is slower. Proper indexing, partitioning, and scheduling significantly improve performance. ON COMMIT ensures real-time data but adds DML overhead, so it must be used carefully.”