Register Login

Difference Between View and Materialized View

Updated Jan 05, 2019

When original tables are stored in databases in their physical form then it is possible to access their attributes directly. However, if the need is to offer restricted access to certain attributes and full access to others, then a virtual table comes in handy to display the necessary attributes only. View and materialized view are two ways in which database users can go about this act. This article will help you understand the key differences between materialized view and view, the meaning of view and its use, the meaning of materialized view and its use, etc.

View vs Materialized View

Comparison Chart

Basis of Differentiation

View

Materialized View

Utility

View is a set of SQL instructions that is used for joining multiple or single tables.

Materialized view is mainly used in context to warehousing of data.

Storage of data

Views are not capable of storing any data, they only point to the data that has to be viewed.

A materialized view is capable of storing data. It makes accessibility to data a faster process.

Manner of use

A view is virtual or logical memory that is based on the ‘select’ query.

A materialized view provides access to duplicate, physical data in a separate table. It serves as a snapshot view of the data, is faster than simple tables and can be used for security purposes.  

DML Commands

In simple views, DML commands are not a possibility in case they ae made using multiple tables.

DML commands are capable of being made in materialized views.

Output

A view uses the output of any given query to make it look like a virtual table.

A materialized view is in the nature of a schema object. It is used for the purpose of summarizing, precomputing, replicating and distributing data. For instance, with the object of constructing a data warehouse.

Where used

A view is capable of being used in the places wherever a table can be put to use.

A materialized view is capable of being stored in a different database or the same database as the base table(s).

Impact on base tables

All operations carried out on a view are capable of impacting the data present in the base table Views are therefore subjected to the integrity constraints as well as the triggers of base tables.

Materialized views offer indirect access to tabular data. They do so by
storing the results of queries in separate schema objects. Thus they are different than ordinary views that do not block storage space or store any data.

Saving/ storage

Views are not stored or saved, they are only used for displaying data.

Materialized views can be saved and stored on the hard disk for later use.

Definition

Views are virtual tables that are developed from a single / multiple views or tables.

Materialized views are physical copies of original tables contained by a database.

Updation

Views are automatically updated every time a virtual view/ table is put to use.

Materialized views have to be updated with the use of triggers or manually.

Processing speed

Views are slow to process.

A materialized view is fast processing. A materialized view log serves as a schema object that records all changes on to the data contained in a master table and can be refreshed incrementally.

Syntax

Create VIEW "VIEW_NAME" as "SQL Statement"

Create Materialized View V Build [clause] Refresh [clause] On [Trigger] As

Where defined

Views merely display the results of query statements.

Materialized views can be defined on views, base tables, partitioned tables, etc. The indexes are then defined on these materialized views for better performance.

Row id

When a view is crated with the use of any table, then the rowid of the view will be the same as the rowid of the original table.

The rowid is different in case of materialized views.

What is a View in Database?

A view is a logical virtual table that can be created by using the ‘select’ query. Views are not stored on the hard disk. The query statement can be fired every time specific data is needed. Therefore, it is possible to get the latest/ updated data at each time from the original table as available in the database. Views allow for the storage of the definition of queries in the database itself.

What is Materialized View in Database?

Materialized Views are basically logical views that are created using the ‘select’ query. However, in their case, the results obtained are saved in a disk or table. As in a view, the query definition is also stored in the database.

Key Differences between Materialized View and View

Views serve to be virtual projections of the result of output queries. They provide a dynamic view of data present in the database for use by those posting the query statements. On the other hand, materialized views can be best described as non-virtual schemas and are associated with database warehousing. Materialized views are mainly utilized for pre-computing, summarizing, replicating and distributing data. Given below are the key differences between views and materialized views:

Access to the data

Views directly affect the data present in base tables because of which they are subjected to triggers and integrity constraints of base tables. Materialized views offer indirect access to base table data by creating separate schemas for storing query results.

Storage space

Views and materialized views differ in the way in which they use storage space. Views, being virtual projections of query results, do not need any storage area. They restrict memory usage and encourage the use of Shared SQL. Conversely, schemas created via Materialized Views need storage space as they may be saved in a different database or the same database in the form of base tables.

Physical copy of data

In a view, users get to see the logical view of tables only. There is no physical copy of data to be availed. The required operations can be performed on the projected view without impacting the original data table in any way. This is not the case in materialized views which are used in cases where Query Response time is of importance as in data warehouses or in case of business intelligence applications.

When to Use Materialized View and View in SQL

Use of Views:

The performance level of each view would depend on the nature of the select query as provided by the user. For instance, the ‘join’ statement is best avoided in case the user desires better performance. In order to establish multiple joins in between tables, it is better to utilize index based columns for the purpose of joining tables. This is because index based columns serve to be faster than the non-index based columns.

A view offers restrictive behaviour in context to its base table.

1. It may be utilized for isolating applications and prohibiting changes in the definition of database tables.

2. It may be used for simplifying SQL statements for better and faster use.

3. Views are also used for enhancing security via restricted access to predetermined sets of rows and columns.

Use of Materialized Views:

It is seen that the overall performance of materialized views is better than that of simple, normal views. This is because the data in a materialized view is saved in a table on the hard disk and the table is usually indexed to facilitate faster access and processing. Additionally, as the joining of tables is carried out at the time of refreshing materialized views, there is no need of firing the join statement as needed in the case of views.

1. The presence of materialized views are transparent to SQL except in case they are looked up for query rewrites. Query rewrites bring about improvements in the performance of SQL query execution; they are best used in data warehouse environments.

2. It is possible to insert, delete, update and add data with the help of updatable materialized views.

Conclusion

Questions related to view vs materialized view remain to be a favourite with interviewers. Regardless of whether you are a developer, programmer or SQL user, it is important to understand the concept of view and materialized views along with their definitions, usage and differences. In case you have any further inputs with regards to materialized view vs view then we would like to hear from you. Do drop your views in the Comments section below.


×