Sunday, April 24, 2016

Difference - View vs Materialized view

The below lists out the differences between View and Materialized view in Oracle.

1.
View acts as an image of a table through which a table contents can be accessed but the result is not stored anywhere on the disk. Hence it is a virtual table.
A materialized view is also similar in this aspect except that the underlying query results are saved to a table. Hence it is physically stored on the database.
2.
A view's results are always the latest as its auto-refreshed each time it is accessed.
A MV needs to be manually refreshed either using the DBMS_MVIEW.REFRESH or periodically refreshed using Oracle scheduler.
3.
Only simple views can be used for DML operations. If the view consists of joins or aggregate functions, its' a complex view. Such views need INSTEAD OF triggers for DML operations.
MV is better suited for DML operations as the main application is data replication.
4.
When we create a view on a table, rowid of view is same as the base table.
Rowid is different.
5.
Lower performance than MV.
Better performance as the data's stored in a table and the table maybe indexed.also joining is done at the time of refresh so no need to fire join statement everytime as in case of view.
6.
Mostly used in applications for data retrieval as no extra space required for storage and simple to maintain.
When performance is key, MVS are used. Hence mainly used in Datawarehousing and OLAP environments.



No comments:

Post a Comment

Note: Only a member of this blog may post a comment.