What is the difference between Views and Materialized Views in Oracle?
Solution 1
Materialized views are disk based and are updated periodically based upon the query definition.
Views are virtual only and run the query definition each time they are accessed.
Solution 2
Views
They evaluate the data in the tables underlying the view definition at the time the view is queried. It is a logical view of your tables, with no data stored anywhere else.
The upside of a view is that it will always return the latest data to you. The downside 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.
Materialized views
They are similar to regular views, in that they are a logical view of your data (based on a select statement), however, the underlying query result set has been saved to a table. The upside of this is that when you query a materialized view, you are querying a table, which may also be indexed.
In addition, because all the joins have been resolved at materialized view refresh time, you pay the price of the join once (or as often as you refresh your materialized view), rather than each time you select from the materialized view. In addition, with query rewrite enabled, Oracle can optimize a query that selects from the source of your materialized view in such a way that it instead reads from your materialized view. In situations where you create materialized views as forms of aggregate tables, or as copies of frequently executed queries, this can greatly speed up the response time of your end user application. The downside though is that the data you get back from the materialized view is only as up to date as the last time the materialized view has been refreshed.
Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables. Materialized views can be incrementally updated by combining them with materialized view logs, which act as change data capture sources on the underlying tables.
Materialized views are most often used in data warehousing / 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.
Materialized views also help to guarantee a consistent moment in time, similar to snapshot isolation.
Solution 3
A view uses a query to pull data from the underlying tables.
A materialized view is a table on disk that contains the result set of a query.
Materialized views are primarily used to increase application performance when it isn't feasible or desirable to use a standard view with indexes applied to it. Materialized views can be updated on a regular basis either through triggers or by using the ON COMMIT REFRESH
option. This does require a few extra permissions, but it's nothing complex. ON COMMIT REFRESH
has been in place since at least Oracle 10.
Solution 4
Materialised view - a table on a disk that contains the result set of a query
Non-materiased view - a query that pulls data from the underlying table
Solution 5
Views are essentially logical table-like structures populated on the fly by a given query. The results of a view query are not stored anywhere on disk and the view is recreated every time the query is executed. Materialized views are actual structures stored within the database and written to disk. They are updated based on the parameters defined when they are created.
juan
Geek (not nerd), who programs stuff. Twitter: @juanformoso https://keybase.io/juan Here are my credentials in the network:
Updated on July 08, 2022Comments
-
juan almost 2 years
What is the difference between Views and Materialized Views in Oracle?
-
Rosdi Kasim about 14 years+1 for the detailed explanation, but what is the downside of query rewrite? If query rewrite will allow Oracle to optimize the query even further then we should ALWAYS enable query rewrite, no?
-
Jeffrey Kemp about 14 yearsThe tables that support a materialized view don't take the same name as the view.
-
Jeffrey Kemp about 14 years@Rosdi, he said it: "the data you get back from the materialized view is only as up to date as the last time the materialized view has been refreshed"
-
Marthinus almost 13 yearsAlso when you need performance on data that don't need to be up to date to the very second, materialized views are better, but your data will be older than in a standard view. Usually BI reports gain a lot of benefit from materialized views.
-
Jeffrey Kemp about 12 years@Marthinus - that is correct except in the case of a materialized view which is REFRESH ON COMMIT - the MV will return exactly the data that has been committed.
-
Kanagavelu Sugumar almost 10 yearsWhat is the meaning of DISK based? Is it mean table is not part of DISK? Is it stored in a file and DISK access is faster that File access ....?
-
Kanagavelu Sugumar almost 10 yearsWhat is the meaning of DISK based? Is it mean table is not part of DISK? Is it stored in a file and DISK access is faster that File access ....?
-
dacracot almost 10 years@KanagaveluSugumar "DISK based" means the data is physically stored on the computer's hard disk.
-
Kanagavelu Sugumar almost 10 years@dacracot Thanks! but that i got it, my question is; Only MView is part of DISK and actual DB tables is not part of DISK..?
-
dacracot almost 10 years@KanagaveluSugumar Yes, the actual tables are written to disk also.
-
Kanagavelu Sugumar almost 10 years@dacracot Thanks! i think you mean to say other than DB tables in the DISK; this MVIEW also maintain a table by resolving all the joins. So that in the runtime single table access is enough; and no need to query multiple tables for join conditions which is usually done by the normal view. Thank you!
-
leqid about 7 years@JeffreyKemp, are you sure? The accepted answer here stackoverflow.com/a/33552513 claims otherwise. Although yeah, this may have been different in 2010 when you commented...
-
Jeffrey Kemp about 7 yearsMy comment above must have been on an older version (probably 9i). You're right, MVs get a table with the same name at least in modern versions.
-
Hybris95 over 6 yearsThere is a "REFRESH ON COMMIT" option that can be specified on a Materialized View
-
Jeremiah Peschka over 6 yearsThanks! I'll update the answer. Do you know when this functionality was added to Oracle?
-
mystery over 3 yearswhat will happen if materialize view's data no longer met where condition ? will the data be deleted upon refresh time ?
-
ERJAN about 3 years@Mike thx u , is there a book or course that explains how to automate this materalized view refresher?
-
Mike Williamson about 3 years@ERJAN, I am not sure if there is a book or course specifically for this. But I would just use a daemon or something similar that can run in the background. I also suspect that specific database engines have specific ways to run scheduled jobs.
-
vpalmerini almost 3 years@ERJAN Here are some options