A structured query language is a vast topic with hell lot of concepts. Today we are going to know the difference between view and materialized view. Before knowing the difference between view and materialized view let us first know what are we talking about exactly.
What is a view?
Views are a logical virtual table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data, so always we get updated or latest data from original tables. It is lightweight and is often the first choice whenever it comes to just presenting data.
The advantage of a view is that it will always return the latest data to you. The disadvantage of a view is that its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly.
Using view for data which is large and require millions of rows to be accessed often degrades performance with high execution time of query.
What is a materialized view?
Materialized views are similar to regular views, just that they are the logical view of your data but the result of the query will get stored in the table or disk, also the definition of the query will also store in the database.
The performance of materialized view is better than a simple view as data is stored in table and tables are indexed much faster for joining. Joining is done at the time of materialized views refresh time so no need to every time fire join statement as in the case of view.
They are periodically updated based upon the query definition. They can be set to refresh manually or automatically. Whenever database detects a change in the underlying table which is powering materialized view, then it can automatically update the changes in the view.
Materialized views are most often used in Business intelligence applications where querying large fact tables with thousands of millions of rows would result in query response times that resulted in an unusable application.
First Difference between view and materialized view:
In Views, the query result is not stored in the disk or database but Materialized view allow to store query result in disk or table. Though additional memory is required storing the result, this results in better execution time of query hence better performance.
When we create the view using any table, rowid of view is same as the original table but in the case of Materialized view rowid is different. Since another table is involved in storing the result of the materialized view, rowid gets changed.
In the case of View, we always get latest data but in a case of Materialized view, we need to refresh the view for getting latest data. If on a schedule updating is set for the materialized view, then this drawback is often ignored.
The performance of View is less than Materialized view.
In the case of a view, it’s only the logical view of the table and no separate copy of table but in the case of Materialized view, we get a physically separate copy of the table.
In the case of Materialized view, we need an extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in the database.
We use views because they are more feasible as we need only logical representation of table data and no extra space is needed. We easily get a replica of data and we can perform our operation on that data without affecting actual table data but when we see a performance which is crucial for the large application we use materialized view where Query Response time matters so Materialized views are used mostly with data warehousing or business intelligence application.