Difference Between View and Materialized View
Views and materialized views, which are also known as Mviews, are two types of database objects in the oracle software. Both of these views, refer to select queries in oracle. What these two views/queries do, is that they act as virtual tables in the software for you.
In a normal scenario, both views and Mviews refer to a large group of queries, which have a number/ set of joins. As a result, it is easy to see that one of the main advantages of using both the views, is to store larger complex queries and select queries as views.
However, there are quite a few differences between the two, which help us differentiate between them.
To start things off, Mviews always store their final product in a snapshot version of the table once created, however, view does not create any tables for you.
View tends to require and takes up very little space to store its data and content. On the other hand, Mviews tend to take up a lot of space, mainly because of the tables that they go on to generate.
If you are to select the same statements, views would take a longer time to execute, as compared to Mviews.
Mviews need to be periodically refreshed in order to update them with the latest data; views update and get the latest data on their own.
Mviews give you the ability to create indexes, however, views do not allow you to create any indexes.
Instructions
-
1
View
Views are virtual tables, which hide selected queries. These queries need to be executed, which is done by selecting a statement from a view. Once we select the statement from a view, it executes it, given that it is inside the view body.
- Image Courtesy: oracleatoz.com
-
2
Materialized view (Mview)
These are a special type of view. Mviews are used when there are issues with views.
When we create an mview, it creates a snapshot table, after it executes a select query. Mviews do not need to re-execute date, should you need it later. However, they cannot be used all the time, since they display the same output which was initially stored as a snapshot table. Their major positive is the speed at which they operate.
- Image Courtesy: blogspot.com
