oracle 什么是物化视图？

What are materialized views?

提问by code511788465541441

Can someone explain to me what views or materialized views are in plain everyday English please? I've been reading about materialized views but I don't understand.

有人可以用简单的日常英语向我解释什么观点或物化观点吗？我一直在阅读有关物化视图的内容，但我不明白。

回答by Jim Hudson

Sure.

当然。

A normal view is a query that defines a virtual table -- you don't actually have the data sitting in the table, you create it on the fly by executing.

普通视图是定​​义虚拟表的查询——实际上表中没有数据，而是通过执行动态创建它。

A materialized view is a view where the query gets run and the data gets saved in an actual table.

物化视图是查询运行并将数据保存在实际表中的视图。

The data in the materialized view gets refreshed when you tell it to.

当你告诉它时，物化视图中的数据会被刷新。

A couple use cases:

几个用例：

• We have multiple Oracle instances where we want to have the master data on one instance, and a reasonably current copy of the data on the other instances. We don't want to assume that the database links between them will always be up and operating. So we set up materialized views on the other instances, with queries like select a,b,c from mytable@masterand tell them to refresh daily.

• Materialized views are also useful in query rewrite. Let's say you have a fact table in a data warehouse with every book ever borrowed from a library, with dates and borrowers. And that staff regularly want to know how many times a book has been borrowed. Then build a materialized view as select book_id, book_name, count(*) as borrowings from book_trans group by book_id, book_name, set it for whatever update frequency you want -- usually the update frequency for the warehouse itself. Now if somebody runs a query like that for a particular book against the book_transtable, the query rewrite capability in Oracle will be smart enough to look at the materialized view rather than walking through the millions of rows in book_trans.

• 我们有多个 Oracle 实例，我们希望在其中一个实例上拥有主数据，并在其他实例上拥有一份合理的当前数据副本。我们不想假设它们之间的数据库链接将始终处于启动和运行状态。因此，我们在其他实例上设置了物化视图，使用类似的查询select a,b,c from mytable@master并告诉它们每天刷新。

• 物化视图在查询重写中也很有用。假设您在数据仓库中有一个事实表，其中包含从图书馆借过的每一本书，以及日期和借书人。并且该员工经常想知道一本书被借了多少次。然后将物化视图构建为select book_id, book_name, count(*) as borrowings from book_trans group by book_id, book_name，将其设置为您想要的任何更新频率——通常是仓库本身的更新频率。现在，如果有人针对该book_trans表针对特定书籍运行这样的查询，Oracle 中的查询重写功能将足够智能以查看物化视图，而不是遍历book_trans.

Usually, you're building materialized views for performance and stability reasons -- flaky networks, or doing long queries off hours.

通常，您是出于性能和稳定性原因构建物化视图——不稳定的网络，或者在下班时间进行长时间的查询。

回答by Ronnis

A viewis basically a "named" SQL statement. You can reference views in your queries much like a real table. When accessing the view, the query behind the view is executed. For example:

一个观点基本上是一个“命名”的SQL语句。您可以像真正的表一样在查询中引用视图。访问视图时，执行视图后面的查询。例如：

create view my_counter_view(num_rows) as
   select count(*)
     from gazillion_row_table;

   select num_rows from my_counter_view;

Views can be used for many purposes such as providing a simpler data model, implement security constraints, SQL query re-use, workaround for SQL short comings.

视图可用于多种用途，例如提供更简单的数据模型、实现安全约束、SQL 查询重用、SQL 缺点的解决方法。

A materialized viewis a view where the query has been executed and the results has been stored as a physical table. You can reference a materialized view in your code much like a real table. In fact, it isa real table that you can index, declare constraints etc. When accessing a materialized view, you are accessing the pre-computed results. You are NOTexecuting the underlaying query. There are several strategies for how to keeping the materialized view up-to-date. You will find them all in the documentation.

物化视图是一种视图，其中已执行查询并将结果存储为物理表。您可以在代码中引用实体化视图，就像真正的表一样。事实上，它是一个真实的表，您可以对其进行索引、声明约束等。访问物化视图时，您正在访问预先计算的结果。您没有执行底层查询。有几种策略可以使物化视图保持最新。您将在文档中找到它们。

Materialized views are rarely referenced directly in queries. The point is to let the optimizer use "Query Rewrite" mechanics to internally rewrite a query such as the COUNT(*) example above to a query on the precomputed table. This is extremely powerful as you don't need to change the original code.

实体化视图很少在查询中直接引用。关键是让优化器使用“查询重写”机制在内部将查询（例如上面的 COUNT(*) 示例）重写为预计算表上的查询。这非常强大，因为您不需要更改原始代码。

There are many uses for materialied views, but they are mostly used for performance reasons. Other uses are: Replication, complicated constraint checking, workarounds for deficiencies in the optimizer.

实体化视图有很多用途，但它们主要用于性能原因。其他用途包括：复制、复杂的约束检查、优化器缺陷的解决方法。

Long version:-> Oracle documentation

长版：-> Oracle 文档

回答by fredt

A view is a query on one or more tables. A view can be used just like a table to select from or to join with other tables or views. A metrialized view is a view that has been fully evaluated and its rows have been stored in memory or on disk. Therefore each time you select from a materialized view, there is no need to perform the query that produces the view and the results are returned instantly.

视图是对一个或多个表的查询。视图可以像表一样用于从其他表或视图中进行选择或与其他表或视图连接。metrialized 视图是一个已经过完全评估并且其行已存储在内存或磁盘上的视图。因此，每次从物化视图中进行选择时，都无需执行生成视图的查询，结果会立即返回。

For example, a view may be a query such as SELECT account, SUM(payment) FROM payments GROUP BY accountwith a large number of payments in the table but not many accounts. Each time this view is used the whole table must be read. With a materialized view, the result is returned instantly.

例如，一个视图可能是一个查询，例如表中SELECT account, SUM(payment) FROM payments GROUP BY account有大量付款但没有很多帐户。每次使用此视图时，都必须读取整个表。使用物化视图，结果立即返回。

The non-trivial issue with materialized views is to update them when the underlying data is changed. In this example, each time a new row is added to the payments table, the row in the materialized view that represents the account needs to be updated. These updates may happen synchronously or periodically.

物化视图的一个重要问题是在底层数据更改时更新它们。在此示例中，每次向支付表添加新行时，实体化视图中代表帐户的行都需要更新。这些更新可能同步或定期发生。

回答by George P. Milliken

Yes. Materialized views are views with a base table underneath them. You define the view and Oracle creates the base table underneath it automatically.

是的。物化视图是在它们下面有一个基表的视图。您定义视图，Oracle 会自动在其下创建基表。

By executing the view and placing the resulting data in the base table you gain performance.

通过执行视图并将结果数据放置在基表中，您可以获得性能。

They are useful for a variety of reasons. Some examples of why you would use a materialized view are:

它们因多种原因而有用。为什么要使用物化视图的一些示例是：

1) A view that is complex may take a long time to execute when referenced

1) 复杂的视图在引用时可能需要很长时间才能执行

2) A view included in complex SQL may yield poor execution plans leading to performance issues

2) 包含在复杂 SQL 中的视图可能会产生糟糕的执行计划，从而导致性能问题

3) You might need to reference data across a slow DBLINK

3) 您可能需要通过慢速 DBLINK 引用数据

A materialized view can be setup to refresh periodically.

物化视图可以设置为定期刷新。

You can specify a full or partial refresh.

您可以指定完全或部分刷新。

Please see the Oracle documentation for complete information

有关完整信息，请参阅 Oracle 文档

回答by Dayakark

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data.

物化视图是包含查询结果的数据库对象。它们是位于远程的数据的本地副本，或者用于根据表数据的聚合创建汇总表。

http://www.oraappdata.com/2016/04/materialized-view.html

http://www.oraappdata.com/2016/04/materialized-view.html