What is Materialized view in SQL | Materialized view example

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 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.

MVs are used in data-warehouse like aggregate materialized views, materialized views 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 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 Deferred: 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.

CLICK HERE TO GET INFORMATION ABOUT COMPLEX SQL  QUERIES

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;

Difference Between Materialized View And View :

Materialized View’s 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.

CLICK HERE TO CHECK COGNIZANT(CTS) INTERVIEW QUESTIONS

Materialized view’s  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-refresh-able 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.

CLICK HERE TO GET 20 MOST IMPORTANT INTERVIEW QUESTIONS

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

HOME