SQL Oracle 中的视图和物化视图有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/93539/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the difference between Views and Materialized Views in Oracle?
提问by juan
What is the difference between Views and Materialized Views in Oracle?
Oracle 中的视图和物化视图有什么区别？
回答by dacracot
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.
视图只是虚拟的，每次访问时都会运行查询定义。
回答by Mike McAllister
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 performancedepends 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.
视图的优点是它总是会向您返回最新的数据。视图的缺点是它的性能取决于视图所基于的选择语句的好坏。如果视图使用的 select 语句连接了许多表，或者使用基于非索引列的连接，则视图的性能可能会很差。
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.
它们类似于常规视图，因为它们是数据的逻辑视图（基于 select 语句），但是，底层查询结果集已保存到表中。这样做的好处是，当您查询物化视图时，您正在查询一个表，该表也可能被索引。
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.
此外，由于所有连接都已在实体化视图刷新时解决，因此您只需支付一次连接的费用（或与刷新实体化视图的频率相同），而不是每次从实体化视图中进行选择。此外，启用查询重写后，Oracle 可以优化从物化视图的源中进行选择的查询，从而改为从物化视图中读取。在将物化视图创建为聚合表的形式或作为频繁执行的查询的副本的情况下，这可以大大加快最终用户应用程序的响应时间。但缺点是，您从实体化视图返回的数据仅与实体化视图上次刷新时的最新数据相同。
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 sourceson 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.
物化视图也有助于保证一致的时间，类似于快照隔离。
回答by Jeremiah Peschka
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 REFRESHoption. This does require a few extra permissions, but it's nothing complex. ON COMMIT REFRESHhas been in place since at least Oracle 10.
当使用带有索引的标准视图不可行或不可取时，物化视图主要用于提高应用程序性能。物化视图可以通过触发器或使用ON COMMIT REFRESH选项定期更新。这确实需要一些额外的权限，但这并不复杂。ON COMMIT REFRESH至少从 Oracle 10 开始就已经存在。
回答by user12786
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.
视图本质上是由给定查询动态填充的类似逻辑表的结构。视图查询的结果不会存储在磁盘的任何位置，每次执行查询时都会重新创建视图。物化视图是存储在数据库中并写入磁盘的实际结构。它们根据创建时定义的参数进行更新。
回答by fn27
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
非物质化视图- 从基础表中提取数据的查询
回答by smshafiqulislam
View:View is just a named query. It doesn't store anything. When there is a query on view, it runs the query of the view definition. Actual data comes from table.
视图：视图只是一个命名查询。它不存储任何东西。当对视图进行查询时，它会运行视图定义的查询。实际数据来自表。
Materialised views:Stores data physically and get updated periodically. While querying MV, it gives data from MV.
物化视图：物理存储数据并定期更新。在查询 MV 时，它提供来自 MV 的数据。
回答by Stew S
Adding to Mike McAllister's pretty-thorough answer...
添加到迈克·麦卡利斯特 (Mike McAllister) 非常详尽的回答中……
Materialized views can only be set to refresh automaticallythrough the database detecting changes when the view query is considered simpleby the compiler. If it's considered too complex, it won't be able to set up what are essentially internal triggers to track changes in the source tables to only update the changed rows in the mview table.
只有当编译器认为视图查询很简单时，才能将物化视图设置为通过数据库检测更改自动刷新。如果它被认为太复杂，它将无法设置本质上是内部触发器来跟踪源表中的更改以仅更新 mview 表中更改的行。
When you create a materialized view, you'll find that Oracle creates both the mview and as a table with the same name, which can make things confusing.
当您创建物化视图时，您会发现 Oracle 将 mview和表创建为具有相同名称的表，这会使事情变得混乱。
回答by Dhirendra Gautam
Materialized views are the logical view of 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.
物化视图是选择查询的数据驱动的逻辑视图，但查询的结果将存储在表或磁盘中，查询的定义也将存储在数据库中。
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.
物化视图的性能比普通视图要好，因为物化视图的数据将存储在表中，并且表可能会被索引，因此加入的速度更快，而且连接是在物化视图刷新时完成的，因此无需每次触发在视图的情况下加入声明。
Other difference includes 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. 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.
其他区别包括在 View 的情况下我们总是获取最新数据，但在 Materialized 视图的情况下我们需要刷新视图以获取最新数据。在实体化视图的情况下，我们需要一个额外的触发器或一些自动方法，以便我们可以保持 MV 刷新，这对于数据库中的视图不是必需的。
