A new refresh option has been introduced to improve materialized view refresh performance and availability in Oracle Database 12c Release 1. This refresh option is called out-of-place refresh. It uses outside tables during refresh instead of the existing “in-place” refresh that directly applies changes to the materialized view container table. This option works with all current refresh methods, such as FAST (‘F’), COMPLETE (‘C’), PCT (‘P’), and FORCE (‘?’). Out-of-place refresh is most useful in situations where there are large amounts of data changes and conventional DML statements do not scale well. It also offers a very high level of availability as the materialized views can be accessed directly and used for query rewrite while the refresh is taking place. It also helps to avoid the materialized view container tables becoming fragmented overtime or unexpected results being returned during refresh.
With this refresh option, the entire or affected portions of a materialized view are computed into one or more outside tables. For partitioned materialized views, if PCT refresh is possible, and local indexes exist on the materialized view, then the same local indexes are created on the outside tables. This refresh is achieved by either switching between the materialized view and the outside table or partition exchange between the affected partitions and the outside tables. During refresh, the direct load is used to populate the outside table, which provides improved performance.
There are three types of out-of-place refresh:
-
Out-of-place Fast Refresh – This offers better availability than the existing fast refresh. It also offers better performance when changes impact a large portion of the materialized view.
- Out-of-place PCT Refresh – This offers better availability than the existing PCT refresh. There are two different methods for partitioned and non-partitioned materialized views. If truncation and direct load are not feasible, then 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.
- Out-of-place Complete Refresh – This offers better availability than the existing complete refresh.
Using DBMS_MVIEW package, with method = ? and out_of_place = true, out-of-place fast refresh are attempted first, then out-of-place PCT refresh, and finally out-of-place complete refresh. An example is the following:
DBMS_MVIEW.REFRESH('MVIEW_NAME', method => '?',
atomic_refresh => FALSE, out_of_place => TRUE);
Restrictions and Considerations with Out-of-Place Refresh
Out-of-place refresh has all the restrictions that apply when using the exiting refresh options. In addition, it has the following restrictions:
- Only materialized join views and materialized aggregate views are allowed.
- No ON COMMIT refresh is allowed.
- No remote materialized views, cube materialized views, object materialized views are allowed.
- No LOB columns are allowed.
- Not allowed with materialized view logs, triggers, or constraints (except NOT NULL) are created on the materialized view.
- Not used for complete refresh within a CREATE or ALTER MATERIALIZED VIEW session or an ALTER TABLE session.
- Atomic mode is not permitted. If you specify atomic_refresh as TRUE and out_of_place as TRUE, an error is returned.
For out-of-place PCT refresh, there is the following restriction:
– No UNION ALL or grouping sets are allowed.
For out-of-place fast refresh, there are the following restrictions:
– No UNION ALL, grouping sets, or outer joins are allowed.
– Not allowed for materialized join views when more than one base table is changed with mixed DML statements
Extra storage is needed for the outside table and the indexes for the duration of the refresh. Therefore, ensure there is enough available space or auto-extend turned on.
The partition exchange in out-of-place PCT refresh impacts the global index on the materialized view. So, if there are global indexes defined on the materialized view container table, Oracle disables the global indexes before doing the partition exchange and the global indexes are rebuilt after the partition exchange. This will cause extra overhead.