We generally use view to provide users a facility to interact with the database and the table design. We also use it to make changes if required. Views ensure integrity and data security by giving users restricted access based on their domain area. However, views are distinguished based on how their infrastructure is designed into two types:
View
Materialized view
The view of a table is a virtual copy of the table generated by the SELECT query; however, the table generated is not stored on the system's hardware. Since every time we need to run the query for view, it always displays the updated data from the table.
The materialized view of a table is also a virtual copy, but the result produced will be stored on the disk as a snapshot or the image of the table. They need to be manually updated by the user. A materialized view can be updated whenever the table is accessed using view, whenever the table itself is altered or can be regularly updated based on need.
The following table illustrates the difference between the view and materialized view:
View | Materialized View |
The result from the view is not stored on the disk; instead, the query expression gets stored on the disk. | Both the query expression and the resulting tuples are stored on the disk. |
It is more cost-effective as the view command doesn't have any associated storage costs. | It's less cost-effective as the materialized view have associated storage costs. |
Since it does not store the tuples generated, the user would need to run the query every time the results are required. In this way, the user always gets an updated table result. | It stores the tuples generated from the query on the disk. Therefore, the tuples need to be updated manually by the user, thus adding the updation cost. |
There is a fixed standard architecture for defining a view. | They don't have a fixed standard of defining the materialized view as a generic architecture defines them. |
They are used when the table is updated regularly and are accessed infrequently. | They're used when the table is less frequently updated but is accessed regularly. |
The query process performs slowly. | The query performance is faster. |
The following diagram shows how a view is created from two tables:
As shown in the diagram above, the view only creates a temporary result, and they are not stored on the disk.
Materialized view generates tuples stored on the disk and can be accessed later. As is shown in the diagram below:
Free Resources