什么是物化视图? [英] What are materialized views?

问题描述

有人可以向我解释一下普通英语每天的观点或具体化观点吗?我一直在阅读有关物化视图的信息，但我不理解.

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.

推荐答案

可以.

普通视图是定​​义虚拟表的查询-您实际上没有将数据放在表中，而是通过执行即时创建它.

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.

几个用例:

• 我们有多个Oracle实例，我们希望在其中一个实例上拥有主数据，并在另一个实例上具有一个合理的当前数据副本.我们不想假设它们之间的数据库链接将始终保持正常运行.因此，我们在其他实例上设置了物化视图，并使用诸如select a,b,c from mytable@master之类的查询，并告诉它们每天刷新.

• 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@master and tell them to refresh daily.

材料化视图在查询重写中也很有用.假设您在数据仓库中有一个事实表，其中每本从图书馆借来的书都带有日期和借阅者.那位员工经常想知道一本书被借了多少次.然后将物化视图构建为select book_id, book_name, count(*) as borrowings from book_trans group by book_id, book_name，将其设置为所需的任何更新频率-通常是仓库本身的更新频率.现在，如果有人针对book_trans表针对特定书籍执行类似的查询，则Oracle中的查询重写功能将足够聪明，可以查看实例化视图，而不用遍历book_trans中的数百万行.

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_trans table, 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.

通常，出于性能和稳定性的原因，您正在构建实例化视图-不稳定的网络，或长时间下班进行查询.

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

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