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.
|
Need that last minute brush up? Searching for PL/SQL Interview questions with answers ? You've come to the right place!
Sunday, April 24, 2016
Difference - View vs Materialized view
The below lists out the differences between View and Materialized view in Oracle.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.