FAST Refresh FAQS

1. What is FAST Refresh in Materialized Views?

FAST Refresh updates only changed data instead of rebuilding the entire view.

• Tracks changes in base tables
• Applies only incremental changes
• Much faster for large datasets

2. How to create a FAST Refresh Materialized View?

CREATE MATERIALIZED VIEW mv_sales_summary

BUILD IMMEDIATE

REFRESH FAST

ON DEMAND

AS

SELECT region_id, SUM(amount) total_sales

FROM sales

GROUP BY region_id;

Key clause: REFRESH FAST

3. Requirements for FAST Refresh

1.    MV Log must exist on base tables

2.    Base table must have primary key OR ROWID support

3.    MV query must follow FAST refresh restrictions

4.    No unsupported constructs (complex outer joins, etc.)

Example MV Log:

CREATE MATERIALIZED VIEW LOG ON sales

WITH PRIMARY KEY INCLUDING NEW VALUES;

4. What is a Materialized View Log?

• Stores primary key values
• Records changed columns
• Tracks DML type (INSERT/UPDATE/DELETE)
• Required for FAST refresh

5. How FAST Refresh works internally

1.    Oracle checks MV logs

2.    Identifies changed rows

3.    Applies incremental inserts/updates/deletes

4.    Updates MV metadata

5.    Clears processed log entries

6. Types of FAST Refresh

• FAST ON DEMAND – Manual/scheduled refresh

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

• FAST ON COMMIT – Refresh after each commit

REFRESH FAST ON COMMIT

7. Restrictions for FAST Refresh

• Simple joins only
• Aggregates must follow rules
• GROUP BY may require primary key columns
• No non-deterministic functions or complex subqueries

Check compatibility:

BEGIN

   DBMS_MVIEW.EXPLAIN_MVIEW('SELECT ...');

END;

8. What if FAST Refresh is not possible?

• REFRESH FORCE → tries FAST, else COMPLETE
• REFRESH FAST → fails if conditions not met

9. FAST vs COMPLETE Refresh

Feature	FAST Refresh	COMPLETE Refresh
Data processed	Incremental changes	Entire table
Speed	Very fast	Slow for large data
Requires MV Log	Yes	No
Resource usage	Lower	Higher
Recommended for	Large tables	Small tables

10. Performance Benefits

• Lower CPU and I/O
• Faster refresh time
• Less locking
• Reduced system load
• Ideal for data warehouses and large reporting systems

11. Common Errors

• Missing MV logs
• Incorrect log definition (no INCLUDING NEW VALUES)
• Missing primary key
• Unsupported SQL constructs
• Complex joins or aggregations not compliant

12. How to check refresh capability

SELECT mview_name, refresh_method, refresh_mode

FROM user_mviews;

 

SELECT mview_name, staleness

FROM user_mviews;

13. INCLUDING NEW VALUES in MV Log

CREATE MATERIALIZED VIEW LOG ON sales

WITH PRIMARY KEY INCLUDING NEW VALUES;

• Required for aggregate MVs
• Stores updated column values
• Needed for SUM, COUNT, etc.

14. Handling Heavy DML

• Large inserts/updates/deletes increase MV log size and redo
• May slow refresh
• Best practice: frequent refresh, prevent log growth

15. Partitioned Tables

• FAST refresh works with partitioned tables
• Use Partition Change Tracking (PCT)
• Refresh only affected partitions

16. Real-World Example

• Sales table 100M rows, 1M daily inserts
• COMPLETE refresh → process 100M rows → slow
• FAST refresh → process only 1M rows → 100x faster

17. Locks

• No long-term locks
• Uses MV logs
• Minimal locking, better concurrency than COMPLETE

18. Performance Tuning Tips

• Index MVs properly
• Use PRIMARY KEY-based logs
• Avoid unnecessary columns
• Schedule refresh during low traffic
• Monitor redo/undo generation
• Partition large base tables

19. When NOT to use FAST Refresh

• Very small tables
• Complex queries
• Real-time not required
• MV log overhead unacceptable

20. Interview Tip

Answer:

“FAST refresh updates only changed rows using materialized view logs instead of rebuilding the entire view. It significantly improves performance for large tables and is commonly used in data warehouse systems.”