Fundamental Differences between Views and Materialized Views in Oracle

1. Introduction

In this article, lets discuss about the difference between the Views and Materialized view in Oracle.

2. Views

Views are a virtual form of a table created by using one or more table(s) or view(s). This virtual table consists of the data retrieved by using the query expression used to define the view.

The data in the view is not actually precomputed or stored in the database. Whenever the view is accessed, the data is computed and the result is provided, thus always providing the updated data from the table.

The below syntax shows the command to create the view.

Create View Viewname As <Query Expression>

In principle, CRUD operations such as Insert, Update, Delete are possible in view and all the operations are performed in the data of the underlying table. On the other hand, views defined with the Group by Clause, Distinct Clause, or Check constraints(If the constraint violated), or read-only option don’t allow the CRUD operations.

3. Materialized Views

Materialized views are physical copy of the underlying database tables. It is like a snapshot. The data in the materialized views are precomputed and stored as an object in the database. Hence, the result from the materialized view is not always up-to-date.

The below syntax shows the command to create the materialized view.

Create Materialized View M_ViewName
Build [clause] Refresh [type]
ON [trigger]
As <query expression>

The materialized views has to be updated manually using triggers or the manual update commands. Build [clause] decides when the data will be populated and Refresh [Type] option in the materialized view creation statement decides when the view will be updated.

Unlike views, CRUD operations are not possible in the materialized views.

4. Key Differences

Lets see what are the key differences between Views and Materialized Views.

TypeViewsMaterialized View
Data Stored PhysicallyNoYes
PerformanceSlowerFaster
Memory UsageNAHigh

5. Conclusion

In conclusion, we have learned about the Views and Materialized views in Oracle. Materialized views are always faster but data may not be up-to-date whereas Views are slower but data is always up-to-date. Users can make the decision on the requirements.

If you still have questions, feel free to ask in our SQL forum.