Materialized View uses and Benefits in Database #DOTC_Mdb

Materialized View uses and Benefits in Database.

Hello Everyone,

Today we will discuss about Materialized view as it’s play important role in database. We already discussed about Simple Views and complex views in my previous blog. Before Materialized view go through my previous blog which related to simple view.

https://dheeraj60.blogspot.com/2020/05/benefits-of-creating-and-using-view-in.html

As we know View is not a database object and not like table which is stored in database, but view can be created from base table a view is an SQL statement that’s stored in the database. This statement, or view, has a name.A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table. In other word we can say View is a virtual/logical table which is basically used for security purpose.

Let’s understand  about  Materialized view :

A materialized view is a view that stores the results of the view’s query. Whenever you query the materialized view, it returns the data stored with the view itself. It’s look like table only. In another word we can say Materialized view is a view that stores the results of the view’s query.

We need to understand the difference between simple view and Materialized view.

The main difference between View and materialized view is that In Views query result is not stored in the disk or database but Materialized view allow storing the query result in disk or table. Other difference between View and materialized view is that, when we create a view using any table, rowid of view is same as the original table but in case of Materialized view rowid is different. So for Materialized view there will be different rowid of columns which is in Hexadecimal Format not the same as its stored rowid in table.

Advantage of Using Materialized View :

1. Improve Query Efficiency : Benefit to using a materialized view is to improve the efficiency of a query. If a query takes a long time to run, it could be because there are a lot of transformations being done to the data by using sub queries, functions, and joins. Materialized view can combine all of that into a single result set that’s stored like a table. This means that any user or application that needs to get this data can just query the materialized view itself, as  all of the data is in the one table, rather than running the expensive query that uses joins, functions, or sub queries. Calculations can also be added to materialized views for any fields you may need, which can save time, and are often not stored in the database. Materialized views can also be created to optimize a single query. They don’t need to be normalized and fit into the overall database design in that way.

2. Simplify a Query

Like a view, a materialized view can also be used to simplify a query. If a query is using a lot of logic such as joins and functions, using a materialized view can help remove some of that logic and place it into the materialized view. So it’s easy to get the result by Materialized view.

Disadvantage of Materialized view:

Disadvantage to using materialized views is that the data needs to be refreshed as it can be done manually, but it should be done automatically. Another Disadvantage we can say data may be inconsistent because the data is stored separately in the materialized view, the data in the materialized view may be inconsistent with the data in the underlying tables it can cause an issue if you are expecting  on data to be consistent.

 

Let’s understand how to create Materialized View:

Syntax:

CREATE MATERIALIZED VIEW dotc

[REFRESH [FAST|COMPLETE|FORCE] [ON DEMAND|ON COMMIT]]

[BUILD IMMEDIATE|BUILD DEFERRED]

AS select_query;

Understanding the Syntax:

CREATE MATERIALIZED VIEW: specify what’s being created.

dotc:  Here dotc is the name of the materialized view.

REFRESH FAST: It uses an incremental refresh method which uses changes made to the underlying tables into a log file.

REFRESH COMPLETE: Uses a complete refresh by re-running the query in the materialized view.

REFRESH FORCE: It indicates that a fast refresh should be performed if possible, but if not, a complete refresh is performed.

REFRESH ON DEMAND:  It indicates that a refresh will occur manually whenever specific package functions are called. On Demand is by default.

REFRESH ON COMMIT: It indicates that a fast refresh occurs whenever a transaction commits that makes changes to any of the underlying tables.

BUILD IMMEDIATE: the materialized view will be populated immediately

BUILD DEFERRED: The materialized view is populated on the next refresh operation.

AS select_query: Any Query, with the results stored in the materialized view.

 

Creating one simple Materialized view by using Join :

CREATE MATERIALIZED VIEW DOTC AS

SELECT

a.emp_id,

a.name,

a.salary,

b.dept_id,

b.dept_name

FROM employee a

INNER JOIN department b ON a.dept_id = b.dept_id;

 

After using above command Materialized view dotc is created and dotc view now can be queried as below :

 

SELECT emp_id,name,salary,dept_id,dept_name

FROM dotc;

Above query will give the result accordingly and give the required result set from underlying table and this view also act like a table as discussed above.

Create one more materialized view by using aggregate function.

CREATE MATERIALIZED VIEW dotc1

REFRESH COMPLETE

AS

SELECT

a.dept_id,

a.name,

COUNT(a.*) AS total_count,

SUM(a.salary) AS total_salary

FROM department a

INNER JOIN employee b ON a.dept_id = b.dept_id

GROUP BY a.dept_id, b.dept_name;

Now, to find this data, all we need to do is query the dotc1 view.

SELECT dept_id,name,total_count,total_salary

FROM dotc1;

We will able to see the records from dotc1 directly .

Note: Data is stored in the materialized view so this can be queried more efficiently.

Hope you are able to understand the uses of Materialized View and how it can be created in databases. Finally we can say Materialized views are useful, helping simplify queries and improve the performance of queries by storing data in an easy-to-use object.

Please go through this blog carefully and let me know if you have any doubts as Materialized view is very important and vastly used in organization.  Also in interview lots of questions being asked from Materialized view. I suggest you to please go through my View blog as well so it’s easy for you to differentiate between the Views and Materialized view as both section is important and so many questions asked in interview from both views and Materialized view.

Thanks.