物化视图与表：优点是什么？
我很清楚为什么物化视图比仅查询基表更可取。不太清楚的是与仅创建另一个具有与 MV 相同数据的表相比的优势。 MV 的唯一优势真的只是易于创建/维护吗？
MV 不是相当于具有匹配架构的表和使用 MV SELECT 语句的 INSERT INTO 吗？
意思是，您可以创建一个 MV，如下所示：
CREATE MATERIALIZED VIEW ... AS
SELECT * FROM FOO;
您可以创建一个等效的表：
CREATE TABLE bar (....);
INSERT INTO bar
SELECT * FROM FOO;
并不是说易于创建/维护还不够，我只是想确保我没有遗漏任何内容。
It's clear to me why a materialized view is preferable over just querying a base table. What is not so clear is the advantage over just creating another table with the same data as the MV. Is the only advantage to the MV really just the ease of creation/maintenance?
Isn't an MV equivalent to a table with matching schema and an INSERT INTO using the MVs SELECT statement?
Meaning, you can create an MV as follows:
CREATE MATERIALIZED VIEW ... AS
SELECT * FROM FOO;
And you can create an equivalent table:
CREATE TABLE bar (....);
INSERT INTO bar
SELECT * FROM FOO;
Not to say that ease of creation/maintenance isn't enough of an advantage, I just want to make sure I'm not missing anything.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（12）
当Oracle遇到复杂查询时，执行该查询将花费更多时间。如果用户想减少执行时间，那么物化视图是最好的选择。首先，我们必须在创建后使用该查询创建物化视图，我们可以简单地使用物化视图视图而不是基表。
When complex queries are encounter with Oracle it will take more time to execute that query .if user want to reduce time of execution then materialized view is best for that .firstly we have to create materialized view with that query after creating we can simply use materialized view instead of base table .
表和 MV 之间的区别在于，使用表时，您可以执行其他用户可以看到的 DML 操作，而您对 MV 所做的更改将无法提供给其他用户，除非您更新数据库服务器。
MV 还有另一个优点，当您使用复杂查询基于多个表构建 MV 时，用户在使用 MV 时性能会大幅提高。
The difference between table and MV is with table , you can do DML operations which will be seen by other users whereas the changes you do to MV will not be available to others until you update your database server.
MV has another advantage when you build MV based on multiple tables using complex queries, the users when using MV the performance increases drastically.
除了其他答案（因为我还没有看到）之外，我想说，虽然它们都占用空间，但物化视图在逻辑上是规范化的，而额外的表在逻辑上是非规范化的。如果这不是临时的、一次性的，那么每当您更新基表时，您都必须记住更新第二个表。
In addition to the other answers (because I haven't seen it), I would say that although they both use up space, the materialized view is logically normalized, whereas the extra table is logically denormalized. If this is something that is not a temporary one-off, you will have to remember to update the second table whenever you update the base table.
实际上，物化视图是需要定期聚合以显示更新结果集的表的最佳选择。
我们可以使用库存模块中数据仓库以外的物化视图来计算每日、每周、每月的库存和期末余额，而不是每次都使用复杂的查询，我们可以制作物化视图来立即获取这些结果。
Materialize views are in fact best choice over tables where aggregations are required regularly to show updated result sets.
We can use Materialized view other than Data ware housing in Inventory modules for calculating daily, weekly, monthly stock with closing balance rather than using complex queries every time , we can make materialized views to fetch such results in no time.
除了已经提到的优点：
我想提一下：
In additition to the already mentionned advantages:
I'd like to mention:
1) 加快写入操作：由于可以在物化视图上创建索引，因此读取速度非常快。请注意，如果您在包含大量写入的表上创建索引，则索引维护开销往往会减慢写入过程。为了避免这种情况，您可以创建一个物化视图并在它们上创建索引。这些索引可以在后台维护，不会对表的写操作产生不利影响。
2) 加速读取操作：复杂连接；通过在物化视图上创建索引可以加快需要很长时间才能运行的数据透视。这在大多数报告场景中变得非常方便。
1) Speeding up write operations: Since indexes can be created on materialized views, reading from them is very fast. Note that if you create an index on a table that includes a lot of writes, index maintenance overhead tends to slow down the write process. To avoid this you can create a materialize view and create indexes on them. These indexes can be maintained in the background and does not adversely affect table write operations.
2) Speeding read operations: Complex joins; pivots that take ages to run can be speed up by creating indexes on the materialized views. This becomes very handy in most reporting scenarios.
我猜正确的比较是：
与：
因为 MV 您可以制作一次，并在需要进行选择时刷新（如果您知道信息更改的频率，甚至可以节省一些调用）
您还可以提供并索引MV，这是你没有其他方式可以得到的东西。当然，这只会有利于 MV 的性能，仅适用于大结果集。
在 postgres 中，你也可以这样做：
通过并行进程刷新信息，而不阻塞需要当前信息的查询。我猜想进行了一些优化以重用正在运行的查询中的内容。
这是 SELECT INSERT INTO 无法做到的。
I guess the correct comparison would be:
versus:
Because the MV you can make it once, and refresh when you need to make the select (and even spare some calls if you know how oft the info changes)
Also you can provide and index to the MV, and that's something that you don't have the other way. Of course that would favor the performance of MV only for big result sets.
In postgres you can do also this:
to refresh the info by a parallel process without blocking the queries that need the current info. I guess that some optimization is done to reuse stuff from the running query.
That's something you can not do with SELECT INSERT INTO.
物化视图将与基础关系保持同步
它所依赖的。
如果物化视图是可更新的，当你修改
物化视图，它也会修改它所依赖的基础关系
取决于。
The materialized view will stay synchronized with the base relations
on which it depends.
If the materialized view is updatable, when you modify the
materialized view, it will also modify the base relation on which it
depends.
物化视图的一大优点是聚合数据的检索速度极快，因为它是预先计算和存储的，但代价是插入/更新/删除。数据库会让物化视图与真实数据保持同步，不需要重新发明轮子，让数据库帮你做。
the big advantage of a Materialized View is extremely fast retrieval of aggregate data, since it is precomputed and stored, at the expense of insert/update/delete. The database will keep the Materialized View in sync with the real data, no need to re-invent the wheel, let the database do it for you.
物化视图可以刷新 - 它们是定期拍摄的数据快照。
您的第二条语句只是一次性交易 - 数据在那一刻插入表中。对原始数据的进一步更改不会反映在表中。
Materialized views can be refreshed - they are snapshots of data taken at regular intervals.
Your second statement is just a one time deal - data gets inserted into Table at that moment. Further changes to the original data do not get reflected in the table.
动态查询重写。物化视图不仅定义关系，还允许您预先计算昂贵的联接和聚合。即使 MV 未在查询中显式使用（给定数据库设置等），优化器也足够智能，可以使用 MV 获取相关数据。
你的问题被标记为 Oracle，但 MSSQL 也有类似的技巧。
Dynamic query rewriting. Materialized views define not only relationships, but also allow you to precompute expensive joins and aggregations. The optimizer is smart enough to use the MV to fetch relevant data even if the MV isn't explicitly used in the query (given DB settings, etc).
Your question was tagged as Oracle, but MSSQL also does similar tricks.
它们基本上是相同的，但是 MV 有各种自动刷新数据的选项，这不仅提高了维护的便利性，而且在某些情况下还提高了效率，因为它可以按行跟踪更改。
They're basically equivalent, but the MV has various options for automatically refreshing the data, which not only improve ease of maintenance but also, in some cases, efficiency, since it can track changes by row.