Materialized view in SQL | Materialized view in Postgres | Materialized view Real Life Examples |View Vs Materialized view

  • Materialized view in SQL :

In this article,I will give the basic information about What exactly the Materialized view in SQL.view are nothing but the logical structure of table which will be created from one table or one or more tables.There are 2 types of views in SQL-

1.Simple View-Simple view has been created on only one table

2.Complex View-Complex views has been created on more than 1 tables


Materialized view in SQL is also a logical structure which is stored physically on the disc.Like a view in Materialized view in SQL we are using simple select statement to create it.You should have create materialized view privileges to create a Materialized view.Definition of Materialized view(called as MV) has been stored in databases.Materialized views are useful in Data-warehousing concepts.When you create a Materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view in SQL data. You must have the privileges necessary to create these objects.Materialized view in SQL

MVs are used in data-warehouse like aggregate materialized views,materialized view with joins etc.

  • Materialized view in SQL Syntax and Examples:

“Materialized views are also know as snapshots..”

Snapshots acts like a physical table because data from snapshots are storing in to physical memory.Snapshot retrieves data very fast.So for performance tuning Snapshots are used.Following is the synax of materialized view:

Create materialized view View_Name
Build [Immediate/Deffered]
Refresh [Fast/Complete/Force]
on [Commit/Demand]
as Select ..........;

Using above syntax you can create materialized views.The Syntax includes some different optional fields:

1.Build Immediate: Means materialized views(mv) created immediately.

2.Build Deffered:Means materialized views(mv) created after one refresh.

3.Refresh on commit:

This option commited the data in materialized view in SQL immediately after data inserted and committed in table.This option is known as incremental refresh option.View is not fully refreshed with this option

4.Refresh on Demand:

Using this option you can add the condition for refreshing data in materialized views.


You can refresh the data using fast (incremental approach),Complete,Force options.


REFRESH complete
on commit SELECT * FROM Employee;

Difference Between Materialized View And View:

View Materialized Views(Snapshots)
1.View is nothing but the logical structure of the table which will retrieve data from 1 or more table. 1.Materialized views(Snapshots) are also logical structure but data is physically stored in database.
2.You need to have Create view privileges to create simple or complex view 2.You need to have create materialized view ‘s privileges to create Materialized views
3.Data  access is not as fast as materialized views 3.Data retrieval is fast as compare to simple view because data is accessed from directly physical location
4.There are 2 types of views:

1.Simple View

2.Complex view

4.There are following types of Materialized views:

1.Refresh on Auto

2.Refresh on demand

5.In Application level views are used to restrict data from database 5.Materialized Views are used in Data Warehousing.

Materialized View Real Life Example:

Materialized Views are basically used in the scenarios where actual performance tuning for query is needed.Materialized views are used mostly in reports where user wants to fetch the records very fast.I will try to explain the real life scenario where exactly materialized view is useful.When user creates materialized view then one table structure is created and user directly fetches that data from that table structure.

Suppose there are 2 tables named Employee and Department. The Employee table contains 1 million records and department table contains 20 records.We need to fetch the Employees associated with that department.

Step 1:

To Perform above scenario we basically create view:

Create View V_Employee


Select E.Employee_num,E.Employee_name,D.Department_Name

from Employee E , Department D where E.Dept_no=D.Dept_no;

Step 2:

Fetch the records from the View

Select * from V_Employee;

It will fetch 10 million records with associated department.But to fetch that records check the time.Let us consider it will take 2 Mins means 120 secs to fetch records

Step 3 :

Let us Create materialized view which will refresh automatically.

Create or Replace Materialized view MV_Employee


Select E.Employee_num,E.Employee_name,D.Department_Name

from Employee E , Department D where E.Dept_no=D.Dept_no

Refresh auto on commit select * from Department;

We have created materialized view in sql for that.and lets check performance.

Select* from MV_Employee;

It will fetch 1 million records in 60 secs.So performance is improved double when you use materialized view.


Materialized view log:

What is Incremental or Fast Refresh?

When in database level some DML changes are done then Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. 

In this situation materialized view is not full refresh and it only refresh the data on incremental basic and only incremental data is added in to materialized view.

What is Complete Refresh?

Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.


Hope you will get actual idea about materialized views in SQL.If you like this article dont forget to comment here.




About admin 111 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development


  1. Found some of the topics quite useful & productive too,, if u can plzz post something different,, use SQL in Excel VBA,, to create projects,, ☺

    • I will Definately write article which is useful for Excel VBA soon.. :)Thanks for visiting my site.I will Add one new tab SQL in Excel VBA in the Website so that it will be useful for the Excel VBA users also..

10 Trackbacks / Pingbacks

  1. SQL Basic Interview Questions for Freshers | Freshers Interview Questions in SQL
  2. Views in SQL |Simple View and Complex View Difference
  3. SQL Interview Questions For Tech Mahindra | Important SQL Interview Questions
  4. OBIEE
  5. Table partition | Table Partitioning in Oracle | Partitioning in SQL | How Partitioning helps improving Performance | Table Partitioning with Examples | Partitioning on existing table
  6. Database Normalization Examples | Normalization in Database with Examples | Database Normalization with Real Life Examples | Designing Normalized Database
  7. Pivot in SQL with Example | Pivot Clause | Pivot and unpivot in SQL | Pivot in Oracle | SQL Pivot with Real Life Example
  8. Interview Questions for TCS | SQL Interview Questions for TCS | Interview Questions Asked in TCS | TCS Interview Questions
  9. SQL Functions List,Functions in SQL,SQL Functions example,Aggregate functions,scalar functions,Aggregate functions in sql,scalar functions in sql,functions
  10. Operators in SQL | SQL Operators | Arithmetic Operators | Logical Operators | Comparison Operators | Negation Operators | Operators in SQL with Examples

Comments are closed.