Major Differences between Views and Materialized Views!.
Views are the virtual projection of an output query or the dynamic view of the data
in a database that is presented to the user whenever requested. Materialized views
on the other hand are a non-virtual schema which is a common part of database warehousing,
primarily used for summarizing, pre-computing, replicating and distributing data etc.
Now having said that, let’s move on to some basic differences between Views and Materialized
views in order to gain a better understanding.
Access to the data
The operations performed using Views directly affects the data in the base table. Therefore they are
subjected to the integrity constraints and triggers of the base table.
Materialized views provide an indirect access to the data of the base table. This is due to
the fact that they create a separate schema to store the results of a query.
One of the common differences between views and materialized views is the way they take
up storage space. Since Views
are the virtual projection of a query result hence they do not
take up any storage area. This helps in reduction of memory usage and encourages the use of Shared SQL.
Whereas the schema created by Materialized Views
take up some storage space as it is saved in either
the same database as its base table or in a different database.
Usage of Views and Materialized Views
Differences between views and materialized views can also be understood in terms of their usages.
have a more restrictive behavior towards a base table.
- It could be used to isolate an application in order to prohibit any change in base table definition.
- It can be used in order to simplify SQL statements for the user.
- Views could be used to enhance security by restricting access to a predetermined set of columns and rows
Existence of Materialized Views
is transparent to the SQL except when used for query rewrites.
- Query rewrites are said to improve the performance of SQL execution and are useful in a data warehouse environment.
- Users can insert, delete and update the data by means of updatable materialized views.
Apart from these differences between views and materialized views, some other important points to
keep in mind which further clears the concept of views and materialized views are:
- Views can be based on each other in addition to operating on base tables. A view can JOIN another view with a table using – GROUP BY or UNION clause.
- Materialized views can be defined on a base table, partitioned table or Views whereas indexes are defined on Materialized views.
- Ultimately a materialized view log is a schema object which records changes to a master table's data so that the materialized view defined on that master table can be refreshed incrementally.