What is difference between View and Materialized View
What is View in database
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.
Performance of the view depends on our select query. If we want to improve the performance of view we should avoid using join statement in our query or if we need multiple joins between table always try to use the index based column for joining as we know index based columns are faster than a non-index based column.
View also allows storing the definition of the query in the database itself.
Performance of the view depends on our select query. If we want to improve the performance of view we should avoid using join statement in our query or if we need multiple joins between table always try to use the index based column for joining as we know index based columns are faster than a non-index based column.
View also allows storing the definition of the query in the database itself.
What is Materialized View in database
Materialized views are also the logical view of our
data-driven by the select query 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.
When we see the performance of Materialized view it is better than normal View because the data of materialized view will be stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.
When we see the performance of Materialized view it is better than normal View because the data of materialized view will be stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.
Difference between View vs Materialized View in database
Based upon on our understanding of
View and Materialized View, Let’s see, some short difference between them :
1) The first difference between View and materialized view is that In Views query result is not stored in the disk or database but Materialized view allow to store the query result in disk or table.
1) The first difference between View and materialized view is that In Views query result is not stored in the disk or database but Materialized view allow to store the query result in disk or table.
2) This is continuation of first difference between View and Materialized View, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table
3) One more difference between View and materialized view in the database is that In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
4) Performance of View is less than Materialized view.
5) Another difference between View vs materialized view is that, when we create a view using any table, rowid of view is same as the original table but in case of Materialized view rowid is different.
6) Last difference between View vs Materialized View is that In 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.
