Difference between Materialized View and View
Views are the most important concept of the database management system. In the interview, it is a popular and commonly asked question, much like truncate vs. delete, correlated, correlated vs. noncorrelated subquery, or primary key vs. unique key. This tutorial will learn about the difference between view and materialized view.
The original table is stored in the physical memory of the database. In which we can access all attributes by running queries. But sometimes, we have to restrict the user to fetch some data from the table and allow them to get only permitted attributes. Suppose we have a student table, and the user can search for student names, marks, branches, ages, and other information about the student. But the user must not be allowed to fetch the student's mobile numbers and addresses.
In such cases, it is a good way to create a view that can show the data of the required attributes of the table. We can achieve such functionality by creating a virtual table or view and Materialized view.
Let's understand the concepts of the views.
What is View?
Views are the virtual table of the database that acts as an actual relation. It is not part of the logical relational model of the database system. They are created using the select query, but the result is not stored in the physical memory. Every time we fire the query to the view, the view returns the updated and latest data from the original table. It means the views are generated every time the view is accessed. In this view, the query definition is stored in the database itself. We can create as many views as we want.
The point is to remember that when we make any change in the virtual table, it will reflect in the original table. If we make any changes in the original or base table, the changes will be reflected in the view. This makes the query performance very slow. For example - we create a view from the join of the two more tables. In this case, we have to resolve the joins and create them every time we try to fetch the data.
However, it provides some advantages, such as it doesn't require memory to store the data. We can create the view using the below syntax.
Note - Views that are created using the DISTINCT clause, Group By clause, check constraint, read only option can't be modified.
Advantages of View
The following are some important advantages of the views.
What is Materialized View?
Materialized views are also known as virtual tables, but the result of the query expression is saved in physical memory. The query definition is also stored in the database. We can also consider them a Physical copy of the original base tables. It is primarily used in the context of warehousing of data. There is no standard view to define materialized view in SQL. However, few database management systems offer custom extensions to use materialized views. Unlike the normal view, they are not updated each time they are used. Instead, we need to update it manually or with the help of the trigger. The process of updating the Materialized view is known as Materialized View Maintenance.
It stores the result in the physical memory, it responds faster than the normal view because the normal view is created whenever we run the query. It is mainly used for summarizing, pre-computing, replicating and distributing data, etc.
Let's understand the syntax of the materialized view.
In the above syntax, the Build clause decides when to populate the materialized view. It contains two options -
Refresh type define the how to update the materialized view. There are three options -
On trigger defines when to update the materialized view. The refresh can be triggered in the two ways -
We have discussed the basic concept of the normal view and materialized view. Now, let's see the difference between normal view and materialized view.
Advantages of Materialized View
The following are the some important advantages of the materialized view.
Difference between View and Materialized View
The following are the important difference between the view and materialized view.
Views are played a crucial role in data retrieval. In this tutorial, we have covered the definition of the views and materialized view and its few important advantages. We have defined both views with a suitable example. Here you can understand the primary difference between both views and can choose according to your requirements.