1  说明

Beginning with Oracle Database 12c Release 1, a new refresh option is available to improve materialized view refresh performance and availability. This refresh option is called out-of-place refresh because it uses outside tables during refresh as opposed to the existing "in-place" refresh that directly applies changes to the materialized view container table. The out-of-place refresh option works with all existing refresh methods, such as FAST ('F'), COMPLETE ('C'), PCT ('P'), and FORCE ('?'). Out-of-place refresh is particularly effective when handling situations with large amounts of data changes, where conventional DML statements do not scale well. It also enables you to achieve a very high degree of availability because the materialized views that are being refreshed can be used for direct access and query rewrite during the execution of refresh statements. In addition, it helps to avoid potential problems such as materialized view container tables becoming fragmented over time or intermediate refresh results being seen.http://www.cndba.cn/Expect-le/article/2038

从12c开始，新增了一个新的刷新物化视图的方法叫Out-of-Place Refresh(不知道咋翻译)，它在刷新时使用外部表，而不是直接将更改应用到物化视图中所涉及的表中。它支持所有的刷新方式FAST ('F'), COMPLETE ('C'), PCT ('P'), 和FORCE ('?')。适用于大量数据变化的情况。

In out-of-place refresh, the entire or affected portions of a materialized view are computed into one or more outside tables. For partitioned materialized views, if partition level change tracking is possible, and there are local indexes defined on the materialized view, the out-of-place method also builds the same local indexes on the outside tables. This refresh process is completed by either switching between the materialized view and the outside table or partition exchange between the affected partitions and the outside tables. Note that query rewrite is not supported during the switching or partition exchange operation. During refresh, the outside table is populated by direct load, which is efficient.

如果物化视图上有索引，那么外部表上也会自动创建索引。通过在物化视图和外部表之间切换，或者受影响的分区和外部表之间的切换来完成刷新操作。注意：在切换和交换期间是不支持查询重写的。而且外部表是通过直接路径插入数据的，所以非常高效。

2  Out-of-Place Refresh类型

有三种刷新类型：

l  out-of-place fast refresh

This offers better availability than in-place fast refresh. It also offers better performance when changes affect a large part of the materialized view.

2  out-of-place PCT refresh

This offers better availability than in-place PCT refresh. There are two different approaches for partitioned and non-partitioned materialized views. If truncation and direct load are not feasible, you should use out-of-place refresh when the changes are relatively large. If truncation and direct load are feasible, in-place refresh is preferable in terms of performance. In terms of availability, out-of-place refresh is always preferable.

3  out-of-place complete refresh

This offers better availability than in-place complete refresh.

 http://www.cndba.cn/Expect-le/article/2038

2.1   启用Out-of-Place Refresh

使用DBMS_MVIEW包来调用Out-of-Place Refresh刷新方式， 设置method = ? 和out_of_place = true。那么Out-of-Place Refresh首先会尝试fast 刷新，然后PCT刷新，最后是complete刷新。

例如：

DBMS_MVIEW.REFRESH('CAL_MONTH_SALES_MV', method => '?', 

   atomic_refresh => FALSE, out_of_place => TRUE);

3  使用Out-of-Place Refresh的限制

Out-of-Place Refresh不仅有in-place刷新的所有限制，而且还有以下限制：

l  Only materialized join views and materialized aggregate views are allowed

2  No ON COMMIT refresh is permitted

3  No remote materialized views, cube materialized views, object materialized views are permittedhttp://www.cndba.cn/Expect-le/article/2038

4  No LOB columns are permittedhttp://www.cndba.cn/Expect-le/article/2038

5  Not permitted if materialized view logs, triggers, or constraints (except NOT NULL) are defined on the materialized view

6  Not permitted if the materialized view contains the CLUSTERING clause

7  Not applied to complete refresh within a CREATE or ALTER MATERIALIZED VIEW session or an ALTER TABLE sessionhttp://www.cndba.cn/Expect-le/article/2038

8  Atomic mode is not permitted. If you specify atomic_refresh as TRUE and out_of_place as TRUE, an error is displayedhttp://www.cndba.cn/Expect-le/article/2038http://www.cndba.cn/Expect-le/article/2038

For out-of-place PCT refresh, there is the following restriction:

9  No UNION ALL or grouping sets are permitted

For out-of-place fast refresh, there are the following restrictions:

l0  No UNION ALL, grouping sets or outer joins are permitted

l1  Not allowed for materialized join views when more than one base table is modified with mixed DML statements

此外，Out-of-place refresh需要额外的空间来存储刷新期间的外部表和索引。out-of-place PCT refresh的分区交换也会影响物化视图上的全局索引，因此oracle建议交换分区之前禁用全局索引，交换完成后重建索引。当然，重建索引是一笔额外的开销。

版权声明：本文为博主原创文章，未经博主允许不得转载。

Out-of-Place Materialized View Refresh