Materialized view in SQL is also a logical structure which
is stored physically on the disc. Like a view in Materialized views in SQL we
are using simple select statement to create it.You should have create
materialized views privileges to create a Materialized views. Definition
of Materialized views (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 views. Oracle
Database uses these objects to maintain the materialized views in SQL data. You
must have the privileges necessary to create these objects.
- Materialized
view in SQL Syntax and Examples:
“Materialized views are also known 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 syntax 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 committed the data in materialized views 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.
Example:
CREATE MATERIALIZED VIEW MV_Employee BUILD immediate
REFRESH complete
on commit SELECT * FROM Employee;
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
as
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
as
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 views 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 views log, Oracle Database must
re-execute the materialized view query to refresh the materialized views. This
process is called a complete refresh. Usually, a fast refresh
takes less time than a complete refresh.A materialized views log is located in
the master database in the same schema as the master table. A master table can
have only one materialized view’s 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 views.
Hope you will get actual idea about materialized
views in SQL.If you like this article do not forget to comment here.
