View and Materialized View

So far, we have talked about original tables stored in the physical form in databases. In which we have access to all the attributes of the tables. What if we have to restrict a user from accessing some attributes of the table and let access the other attributes.

Like, a clerk in an administrative department can search the name, address, designation, age and such other factors of an employee table. But he must not be authorized to view or access the salary of any employee.

In such cases, we must be able to create a virtual table that can only display the required attributes from a table. This is possible via View and Materialized View which we will discuss in this article. We will also discuss the differences between view and materialized View with the help of comparison chart shown below:

Comparison Chart

Basis for Comparison	View	Materialized View
Basic	A View is never stored it is only displayed.	A Materialized View is stored on the disk.
Define	View is the virtual table formed from one or more base tables or views.	Materialized view is a physical copy of the base table.
Update	View is updated each time the virtual table (View) is used.	Materialized View has to be updated manually or using triggers.
Speed	Slow processing.	Fast processing.
Memory usage	View do not require memory space.	Materialized View utilizes memory space.
Syntax	Create View V As	Create Materialized View V Build [clause] Refresh [clause] On [Trigger] As

Definition of View

View is a virtual table, created using Create View command. This virtual table contains the data retrieved from a query expression, in Create View command. View can be created from one or more than one base tables or views. A view can be queried like you query the original base tables.

It is not that the View is precomputed and stored on the disk instead, a View is computed each time it is used or accessed. Whenever a view is used the query expression in Create View command is executed at that particular moment. Hence, you always get the updated data in a View.

If you update any content in View, it is reflected in the original table, and if any changes had been done to the original base table, it would reflect in its View. But this makes the performance of a View slower. For example, a view is created from the join of two or more tables. In that case, you have to pay time to resolve Joins each time a View is used.

But it has some advantages like it do not require storage space. You can create a customized view of a complex database. You can restrict the user from accessing sensitive information in a database. Reduces the complexity of queries by getting data from several tables into a single customized View.

Now Let us see the syntax of  View

Create View V As <Query Expression>

Remember all View are not updateable. Like a View created using DISTINCT clause, Group By clause, CHECK constraint (if check constraints violate), Read-only option can’t be updated.

Definition of Materialized View

Materialized View is the Physical copy of the original base tables. The Materialized View is like a snapshot or picture of the original base tables. Like View, it also contains the data retrieved from the query expression of Create Materialized View command.

But unlike View, the Materialized View are precomputed and stored on a disk like an object, and they are not updated each time they are used. Instead, the materialized view has to be updated manually or with the help of triggers. The process of updating the Materialized View is called Materialized View Maintenance.

Materialized View responds faster in comparison to View. It is because the materialized view is precomputed and hence, it does not waste time in resolving the query or joins in the query that creates the Materialized View. Which in turn responses faster to the query made on materialized view.

Let us check the syntax of Materialized View:

Create Materialized View V
Build [clause] Refresh [ type]
ON [trigger ]
As <query expression>

Where Build clause decides, when to populate the Materialized View. Refresh type decides how to update the Materialized View and trigger decides when to update the materialized View.

Materalized Views are generally used in the data warehouse.

Key Differences Between View and Materialized View

1. The basic difference between View and Materialized View is that Views are not stored physically on the disk. On the other hands, Materialized Views are stored on the disc.
2. View can be defined as a virtual table created as a result of the query expression. However, Materialized View is a physical copy, picture or snapshot of the base table.
3. A view is always updated as the query creating View executes each time the View is used. On the other hands, Materialized View is updated manually or by applying triggers to it.
4. Materialized View responds faster than View as the Materialized View is precomputed.
5. Materialized View utilizes the memory space as it stored on the disk whereas, the View is just a display hence it do not require memory space.

Conclusion

Materialized View responds faster as compared to View. But View always provides up to date information to the user.