SQL 物化视图与表：有什么优势？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4218657/
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
Materialized View vs. Tables: What are the advantages?
提问by seth
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?
我很清楚为什么物化视图比仅查询基表更可取。不太清楚的是，与仅使用与 MV 相同的数据创建另一个表的优势。MV 的唯一优势真的只是易于创建/维护吗？
Isn't an MV equivalent to a table with matching schema and an INSERT INTO using the MVs SELECT statement?
MV 不等同于使用 MVs SELECT 语句具有匹配模式和 INSERT INTO 的表吗？
Meaning, you can create an MV as follows
意思是，你可以创建一个MV如下
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.
并不是说创建/维护的简便性不足以成为优势，我只是想确保我没有遗漏任何东西。
回答by Donnie
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).
动态查询重写。物化视图不仅定义关系，还允许您预先计算昂贵的连接和聚合。优化器足够聪明，可以使用 MV 来获取相关数据，即使 MV 未在查询中明确使用（给定的 DB 设置等）。
Your question was tagged as Oracle, but MSSQL also does similar tricks.
您的问题被标记为 Oracle，但 MSSQL 也有类似的技巧。
回答by Dave Costa
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.
它们基本上是等价的，但是 MV 有多种选项可以自动刷新数据，这不仅提高了维护的便利性，而且在某些情况下还提高了效率，因为它可以逐行跟踪更改。
回答by Roopesh Shenoy
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.
您的第二条语句只是一次性交易 - 在那一刻数据被插入到表中。对原始数据的进一步更改不会反映在表中。
回答by KM.
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.
物化视图的一大优势是聚合数据的检索速度非常快，因为它是预先计算和存储的，以插入/更新/删除为代价。数据库会让实体化视图与真实数据保持同步，无需重新发明轮子，让数据库为您完成。
回答by Rose Perrone
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.
物化视图将与它所依赖的基础关系保持同步。
如果物化视图是可更新的，当你修改物化视图时，它也会修改它所依赖的基础关系。
回答by jrouquie
In additition to the already mentionned advantages:
除了已经提到的优点：
- dynamic query rewriting (in short, the DB optimizer knows how the MV is created, so it can reuse it to optimize other queries),
- optional, automatic, possibly incremental refresh,
- 动态查询重写（简而言之，DB 优化器知道 MV 是如何创建的，因此它可以重用它来优化其他查询），
- 可选的，自动的，可能是增量刷新，
I'd like to mention:
我想提一下：
- some materialized views can be written to, which updates the source table (for instance joins with primary keys can be written to, on the opposite if the materialized view is the result of a group by it can't be written to)
- the DB server retains the query that created the data and can rerun it. If you create a table, you need an external tool (possibly just a custom script) to rerun the query whenever a refresh is needed / asked by the user. (I work for a company developing a tool that does that and much more).
- 可以写入一些物化视图，这会更新源表（例如可以写入带有主键的连接，相反，如果物化视图是无法写入的组的结果）
- 数据库服务器保留创建数据的查询并可以重新运行它。如果您创建一个表，您需要一个外部工具（可能只是一个自定义脚本）来在用户需要/要求刷新时重新运行查询。（我为一家开发工具的公司工作，该工具可以做到这一点等等）。
回答by yucer
I guess the correct comparison would be:
我想正确的比较是：
REFRESH MATERIALIZED VIEW bar;
versus:
相对：
CREATE TABLE bar (....);
INSERT INTO bar
SELECT * FROM FOO;
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)
因为 MV 您可以制作一次，并在需要进行选择时刷新（如果您知道信息更改的频率，甚至可以省去一些电话）
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.
此外，您还可以提供 MV 并为其编制索引，这是您无法通过其他方式实现的。当然，这将有利于 MV 仅对大结果集的性能。
In postgres you can do also this:
在 postgres 中，你也可以这样做：
REFRESH MATERIALIZED VIEW CONCURRENTLY bar;
to refresh it by two parallel process if one has not ended and the other needs the info up to that instant in time. 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.
这是 SELECT INSERT INTO 无法做到的。
回答by Sriwantha Attanayake
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.
1) 加速写入操作：由于可以在物化视图上创建索引，因此读取它们的速度非常快。请注意，如果您在包含大量写入的表上创建索引，则索引维护开销往往会减慢写入过程。为避免这种情况，您可以创建一个实体化视图并在其上创建索引。这些索引可以在后台维护，不会对写表操作产生不利影响。
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.
2) 加速读操作：复杂的连接；可以通过在物化视图上创建索引来加速运行需要很长时间的枢轴。这在大多数报告场景中变得非常方便。
回答by orbfish
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.
除了其他答案（因为我还没有看到），我想说虽然它们都占用了空间，但物化视图在逻辑上是规范化的，而额外的表在逻辑上是非规范化的。如果这不是一次性的，那么您必须记住在更新基表时更新第二个表。
回答by vettipayyan
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.
table 和 MV 之间的区别在于 table ，您可以执行其他用户可以看到的 DML 操作，而您对 MV 所做的更改将无法用于其他人，直到您更新数据库服务器。
MV has another advantage when you build MV based on multiple tables using complex queries, the users when using MV the performance increases drastically.
当您使用复杂查询基于多个表构建 MV 时，MV 有另一个优势，用户在使用 MV 时性能急剧增加。
