12C Out-of-Place Refresh Option (Materialized Views)

Refreshing Materialized views at a fast speed in Oracle has always been a challenge and Oracle 12C has taken this 1 step ahead with the new enhancement in 12C for materialized view in the form of out of place refresh.

Oracle provides a package DBMS_MVIEW which has three APIs for performing refresh operations :

1) DBMS_MVIEW.REFRESH :- Refresh one or more materialized views.

2) DBMS_MVIEW.REFRESH_ALL_MVIEWS :- Refresh all materialized views.

3) DBMS_MVIEW.REFRESH_DEPENDENT :- Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views.

New enhancement in 12C for materialized view which is out of place refresh is really a great feature of Oracle 12C , in this type of refresh oracle uses outside tables and they are used to improve materialized view availability and the refresh performance in certain situations.

Because oracle uses outside tables Out of place refresh requires extra storage space and the indexes for the duration of the refresh. Therefore, you must have enough available tablespace space or auto extend should be turned on.

To speed up the same operation oracle internally uses partition exchange techniques to improve performance and availability of the materialized views.

The partition exchange in out-of-place PCT refresh impacts the global index on the materialized view. Therefore, if there are global indexes defined on the materialized view container table, Oracle disables the global indexes before doing the partition exchange and rebuild the global indexes after the partition exchange. This rebuilding is additional overhead.

Using the refresh interface in the 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('CAL_MONTH_SALES_MV', 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 corresponding in-place refresh. In addition, it has the following restrictions:

## Only materialized join views and materialized aggregate views are allowed

## No ON COMMIT refresh is permitted

## No remote materialized views, cube materialized views, object materialized views are permitted

## No LOB columns are permitted

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

## Not permitted if the materialized view contains the CLUSTERING clause

## Not applied to 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 displayed

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

## No UNION ALL or grouping sets are permitted

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

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

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