What is a Materialized View and How Does it Differ from a Regular View?
💡 Concept Name
Materialized View is a database object that stores the result of a query physically, unlike a regular view that is virtual and recomputed on each access.
📘 Quick Intro
Regular views are virtual tables—they store the SQL definition, not the data. Materialized views store actual data and can be refreshed periodically or manually, making them useful for performance-heavy reports.
🧠 Analogy / Short Story
Think of a regular view as a live security camera feed—it shows real-time data but needs to process it each time. A materialized view is like a snapshot—you save a frame that you can reuse until you refresh it again.
🔧 Technical Explanation
- 📊 Regular views are just SQL SELECT queries stored for reuse.
- 💾 Materialized views store the actual result set in a table-like structure.
- ♻️ Materialized views can be refreshed automatically or manually.
- ⏱️ They offer faster reads but may serve stale data if not refreshed.
- 🔐 Useful in OLAP systems or dashboards where real-time data isn’t critical.
🎯 Purpose & Use Case
- ✅ Use regular views when data must always reflect the current state.
- ✅ Use materialized views to speed up complex joins or aggregations.
- ✅ Materialized views help cache expensive queries in data warehouses.
💻 Real Code Example
-- Regular View (Virtual)
CREATE VIEW SalesView AS
SELECT Region, SUM(Amount) AS Total
FROM Sales
GROUP BY Region;
-- Materialized View (Physical, Oracle/PostgreSQL Syntax)
CREATE MATERIALIZED VIEW SalesSummary AS
SELECT Region, SUM(Amount) AS Total
FROM Sales
GROUP BY Region
WITH DATA;
-- Refresh Option
REFRESH MATERIALIZED VIEW SalesSummary;
❓ Interview Q&A
Q1: What is a materialized view?
A: It’s a view that stores query results physically and can be refreshed manually or automatically.
Q2: How is it different from a regular view?
A: A regular view only stores the query; a materialized view stores the query’s result set.
Q3: Can a materialized view be outdated?
A: Yes, if not refreshed, it may contain stale data.
Q4: Are materialized views supported in all databases?
A: No, support varies; PostgreSQL and Oracle support them natively.
Q5: Why use a materialized view?
A: To improve performance on complex queries that don’t require real-time data.
📝 MCQs
Q1. What does a materialized view store?
- Just query definition
- Query result data
- Only keys
- Stored procedures
Q2. Which view is recalculated every time it's queried?
- Materialized view
- Regular view
- Index view
- Stored table
Q3. Which view can be refreshed manually?
- Regular view
- Materialized view
- Pivot view
- None
Q4. When should you use materialized views?
- For simple queries
- For schema changes
- For performance-heavy queries
- For user input
Q5. Which is more real-time accurate?
- Materialized view
- Regular view
- Static table
- Snapshot view
💡 Bonus Insight
Use indexing on materialized views for even faster access. However, weigh storage cost and freshness needs before choosing them over regular views.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!