Difference between Views and Materialized Views in Oracle database

Why Materialized views is preferred over views - It is commonly asked interview question for senior developer.In this post we will list out benefits of Materialized view over views.

View is static SELECT query stored as database object.The tables or views used to create any view are termed as base tables or views. In other words, select statement are logical/virtual table which is accountable for collecting data from database tables and return as virtual table(virtual table because it can be used as database table- perform update and delete operation with some restriction).
Refer more details about syntax and semantics of view creation.

Materialized view is a database object that contains the results of a query not just query as contrast to view.The tables,views or materialized views used in FROM clause of the query are termed as master table and database where these objects exist are called materialized view.Refer following for general syntax of materialized view creation.

Comparison Views and materialized views :- 

1. View is just logical table-like structure (select query) populated on the fly, when the view query is executed. The results of a view query are not stored anywhere on disk and the view(logical table) is recreated every time the query is executed.
   On the other hand materialized views are actual structures stored within the database and written to disk. It is populated based on clauses defined while creation.
2. Views are non-indexed, since it is created from the underlying base table, however materialized views are indexed. so performance of views may be poor if join is not performed on non-indexed column of base table.Materialized views can be indexed because result set are actually stored in hard disk,so it can out perform views. 
3. Views are simple and less over head associated but less efficient. Materialized views are primarily used to increase application performance because of indexing support, a overhead associated with it is to keep it updated using trigger or some other method.
4. Views always return updated result set from database tables or views, however materialized views gives result sets refreshed until last time.
5. Join operation is a costly affair in database.In views all the joins are performed at the time view query is executed and it may degrade performance, if poorly join operation is performed. However, in materialized view join operation is carried out at refresh time.So, the cost of join operation is relatively less than views.(Order of number of times it is refreshed.)

Materialized view Refresh:-  When DML(update,delete, insert) operations are performed on master table, on which materialized view has been created, materialized view need to be refreshed. In oracle database this refresh can be performed in following ways:-

1. Incremental or fast refresh:- When DML statements are executed oracle database maintain materialized view log containing information about rows modified and if refresh operation is performed using materialized view log, it is termed as incremental or fast refresh.  
2. Complete refresh:- When without using materialized view logs refresh is performed, materialized query is executed again and this mechanism is termed as complete refresh.
3. Force refresh:- When Force clause is used for refresh, it specify that, when a refresh occurs, Oracle Database will perform a fast refresh if possible(materialized log is existing) or a complete refresh if fast refresh is not possible.If we do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.

Note:- Complete refresh is more time consuming than fast refresh. Materialized view log is a overhead that must be created for all tables participating in Join operation in materialized view query.

---

Read also:
1. Difference between View and Synonym in Oracle database.
2. Internals of PL/SQL execution in database server.