REAL-TIME REFRESHABLE MATERIALIZED VIEWS – ORACLE 12cR2 NEW FEATURE

Introduction

Oracle 12.2 introduced the concept of real-time materialized views, which allow a statement-level wind-forward of a stale materialised view, making the data appear fresh to the statement. A Materialized View that is STALE can still speed up queries while delivering correct results. The data from the stale MV is then on the fly combined with the change information from MV logs in an operation called ON QUERY COMPUTATION. The result is delivered slightly slower as if the MV were FRESH, so there is some overhead involved in the process. But it should be noticeable faster than having to do Full Table Scans as it was required in versions before 12c in that situation.

Before	After

Real-time materialized views

Even when the materialized view is stale, we can get fresh result without querying the source tables. We have the stale values in the materialized view and we have all changes logged into the materialized view log. Easy or not, merging that can be computed to get fresh result. We still need fast refresh but we don’t need refresh on commit anymore:SQL> alter materialized view TEST_MVIEW refresh on demand; Materialized view altered.

And in order to use this new feature we have to enable it a materialized view level:SQL> alter materialized view TEST_MVIEW enable on query computation; Materialized view altered.

The FRESH_VM hint tells Oracle we want to take advantage of the real-time functionality when doing a direct query against the materialized view

Converting an Existing Materialized View into a Real-time Materialized View

If the prerequisites for a real-time materialized view are met, then an existing materialized view can be converted into a real-time materialized view by altering its definition and enabling on-query computation.

To convert a materialized view into a real-time materialized view:

Modify the materialized view definition and enable on-query computation by using the ON QUERY COMPUTATION clause in the ALTER MATERIALIZED VIEW statement.

You can convert a real-time materialized view into a regular materialized view by disabling on-query computation using the DISABLE ON QUERY COMPUTATION clause in the ALTER MATERIALIZED VIEW statement.

The materialized view TEST_MV is based on the ORDERS, ITEMS, and PRODUCTS tables and uses fast refresh. Materialized view logs exist on all three base tables. You want to modify this materialized view and convert it into a real-time materialized view.

Modify the materialized view definition and include the ON QUERY COMPUTATION clause to change it into a real-time materialized view.
ALTER MATERIALIZED VIEW TEST_MV ENABLE ON QUERY COMPUTATION;
Query the DBA_MVIEWS view to determine if on-query computation is enabled for TEST_MV.
SELECT mview_name, on_query_computation FROM dba_mviews WHERE mview_name = 'TEST_MV';

DBADEEDS