Categories: Update

Difference Between View and Materialized View

Table of Contents

View vs Materialized View
 

Views and materialized views (mviews) are two types of oracle database objects. Both of these objects refer select queries. These select queries act as virtual tables. Normally views and mviews refer large select queries, which have set of joins. Therefore, one of the main advantages of views is, we can store complex select queries as views. Hence, we can hide the logic behind the select queries from its end users. When we need to execute the complex select statement, just we have to execute

                      select * from viewname

View

As mentioned before, view is a virtual table, which hides a select query. These select queries are not pre-executed. When we execute a select statement from a view, it executes the select statement that is inside the view body. Let us assume the select statement of the view body as a very complex statement. So when it is executed, it takes some time to execute (relatively more time). In addition, view uses very small space to store itself. That is because it has only a select statement as its content.

Materialized View (Mview)

This is a special type of view. Mviews are created when we have performance issues with views. When we create a mview, it executes its select query and stores its output as a snapshot table. When we request data from Mview, it does not need to re execute its select statement. It gives the output from its snapshot table. Therefore, the execution time of mview is lesser than view (for same select statement). However, mviews cannot be used all time, as it shows the same output, which is stored as a snapshot table. We should refresh the mview to get its latest result set.

What is the difference between View and Mview?

1. Mview always stores its output as a snapshot table when it is created, but view does not create any tables.

2. View does not need big space to store its content, but mview needs relatively larger space than a view to store its content (as a snapshot table). 

3. View takes larger execution time, but mview takes smaller execution time than views (for the same select statement).

4. Mviews need to be refreshed to get its latest data, but views always give its latest data.

5. The schema needs “create materialized view” privilege to create mviews, and for views, it needs “create view” privilege.

6. Indexes can be created on mviews to gain more performance, but indexes cannot be created on views.

ncG1vNJzZmivp6x7pbXFn5yrnZ6YsqOx07CcnqZemLyue8OinZ%2Bdopq7pLGMm5ytr5Wau27CyJ6uZpmemXq3v4ymmK2dop6urbXZnptmrpmaxHA%3D