物化视图与表格:有什么优点? [英] Materialized View vs. Tables: What are the advantages?

问题描述

对我来说很清楚，为什么物化视图比查询基表更可取.尚不清楚的是，仅使用具有与MV相同的数据的另一个表的优势. MV的唯一优势真的就是易于创建/维护吗?

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是否不等同于具有匹配模式并使用MVs SELECT语句进行INSERT INTO的表?

Isn't an MV equivalent to a table with matching schema and an INSERT INTO using the MVs SELECT statement?

意思是，您可以按照以下步骤创建MV

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.

推荐答案

动态查询重写.物化视图不仅定义关系，而且还允许您预先计算昂贵的联接和聚合.即使没有在查询中明确使用MV(给定数据库设置等)，优化器也足够聪明，可以使用MV来获取相关数据.

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).

您的问题被标记为Oracle，但是MSSQL也具有类似的技巧.

Your question was tagged as Oracle, but MSSQL also does similar tricks.

这篇关于物化视图与表格:有什么优点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！