Oracle-物化视图在完全刷新期间仍可访问.这是如何运作的? [英] Oracle - Materialized View still accessible during complete refresh. How does this work?

问题描述

在我们的一个应用程序中，我们有一个巨大的物化视图，该视图每天刷新3次，刷新需要7个小时. (不理想，我知道).这让我感到困惑，因为我确实认为用户和会话在刷新时无法访问该物化视图，但显然可以！ (刷新类型为完整刷新)

In one of our applications, we have a massive Materialized View that refreshes three times a day, and takes seven hours to refresh. (Not ideal, I know). This perplexed me, because I surely thought that users and sessions could not access this materialized view while it was being refreshed, but apparently they can!. (The type of refresh is a complete refresh)

据我所知，在完全刷新期间，将删除现有数据集，然后重新执行查询.如果是这样，那么在刷新实例化视图时，用户/其他会话如何能够访问实例化视图?

During a complete refresh, to my understanding, the existing dataset is dropped and the query is then re-executed. If this is true, then how are users/other sessions able to access the materialized view while the materialized view is being refreshed?

推荐答案

可以通过两种不同的方式进行完全刷新-原子刷新或非原子刷新.原子刷新仅发出DELETE来删除实例化视图中的所有行，然后执行INSERT插入新数据.这一切都在一个事务中完成，因此Oracle的标准多版本读取一致性体系结构使Oracle可以向其他会话显示旧数据，直到刷新完成为止.在非原子刷新中，Oracle在实例化视图上执行TRUNCATE，然后在直接路径INSERT上插入新数据.这实际上效率更高，但是由于TRUNCATE是DDL，这意味着刷新期间其他会话看不到旧数据.

There are two different ways that a complete refresh can happen-- an atomic refresh or a non-atomic refresh. An atomic refresh simply issues a DELETE to delete all the rows in the materialized view and then does an INSERT to insert the new data. This is all within a single transaction so Oracle's standard multi-version read consistency architecture lets Oracle show other sessions the old data until the refresh completes. In a non-atomic refresh, Oracle does a TRUNCATE on the materialized view and then a direct-path INSERT to insert the new data. This is substantially more efficient but since TRUNCATE is DDL, it means that the old data is not visible to other sessions during the refresh.

这篇关于Oracle-物化视图在完全刷新期间仍可访问.这是如何运作的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！