Before discussing Materialized Views, we must first understand what a View is in SQL.
What is View?
The view is nothing but a virtual table that does not physically exist in the database. It doesn’t store data physically itself; whenever an underlying query is fired, the view runs and produces a dynamic result set.
Syntax: create or replace view view_name as select query;
Types Of Views:
There are 4 types of views:
- Simple view: A simple view is based on a single base table; it should not contain joins, group by clauses, or other functions. It can be updateable.
- Complex view: a complex view consists of multiple tables, joins, group functions, subqueries, etc.
- Materialized view: A Materialized View is a database object that stores the results of a query physically on disk, similar to a table. It is used to improve query performance by storing precomputed, summarized, or aggregated data, and can be refreshed periodically to keep the data up to date.
🔒 Limitations of Views in Oracle SQL
- Not all views are updatable:
- Views with
GROUP BY,DISTINCT,UNION,JOINSubqueries or aggregate functions are not updatable. - Use INSTEAD OF triggers to allow DML on complex views.
- Views with
- Cannot create indexes on views:
- Views rely on indexes of underlying base tables.
- No parameters allowed:
- Views cannot accept input arguments, such as functions or procedures.
- Performance concerns:
- Views are evaluated at query time (unless materialized), which can be inefficient for complex or nested views.
ORDER BYnot allowed in view definitions:- Unless used with
FETCH FIRST(Oracle 12c+) or inside subqueries.
- Unless used with
- Security is limited:
- Views do not enforce row-level security.
- Users with access to base tables can bypass view restrictions unless properly managed with GRANTs or VPD.
- Dependent on base tables:
- Changes (e.g., DROP or ALTER) to base tables can invalidate the view.
- No duplicate column names:
- All columns in a view must have unique aliases/names.
- Materialized views have additional restrictions:
- Require specific conditions for fast refresh.
- Not real-time unless refreshed frequently.
- Cannot grant more privileges through views:
- Views can’t elevate access beyond what users already have.
What is a materialized View?
A materialized view is a database object that stores the result set of the query physically on disk; it’s quite similar to a table. It is used to enhance the query performance by storing precomputed, summarized, or aggregated data, and can be refreshed periodically to keep the data up to date.
🔹 Key Characteristics:
- Can support query rewrite, allowing Oracle to use the materialized view automatically in place of the base tables.
- Unlike regular (virtual) views, materialized views store data.
- Can be refreshed automatically (on a schedule or on commit) or manually.
- Often used in data warehousing and reporting to avoid expensive query recalculations.
Syntax:
create or replace materalized view view_name
build(immediate/deferred)
refresh (fast/complete/force)
on (commit/demmand)
start with <start date> next<end date>
(enable/disable) query rewrite
as
select...;Breaking the above Syntax:
- Build Immediate(default): The view is getting populated with results immediately.
- Build deferred: the view is created, but its data is not populated until the first refresh operation is explicitly performed.
- Fast Refresh: The materialized view is refreshed incrementally. It means you perform update/insert/delete only on the rows that have been changed on the master tables.
- Complete Refresh: means the view is completely refreshed by re-running the entire query.
- Force Refresh: Oracle will attempt to use the fast refresh method if possible; if not, it will fall back to a complete refresh.
- On Demand: View is refreshed manually using the DBMS_MVIEW.REFRESH
| View | Materialized View |
|---|---|
| A View is like a live window into your data. It doesn’t store any data itself; instead, it just stores a SQL query. Every time you access the view, the database runs the query in real time and shows the most current results. | A Materialized View is like a saved snapshot of the data at a certain point in time. It stores the actual result of the query, so when you access it, you’re reading stored data — not re-running the query. |
| Great when you need real-time, always up-to-date data, especially when the underlying data changes frequently. | Ideal when you want fast access to data, especially when the original query is heavy or slow to compute every time. |
| Since it runs the query every time, it can be slower for complex queries or large datasets. | Since the data is already stored, it’s much faster to read — but it might not reflect the most recent changes unless refreshed. |
| Does not use extra storage, since it doesn’t save data — only the query logic. | Uses extra storage, because it holds actual data — just like a table. |
| Always up-to-date, because it fetches fresh data from the source. | Can become stale/outdated if not refreshed manually or on a set schedule. |
| Useful for reporting, dashboards, or real-time monitoring. | Useful for performance-heavy reports, analytics, or periodic summaries. |
Also Must Read: Cursor in PL/SQL