Materialized view refresh in Oracle database

 Materialized view refresh in Oracle database

Introduction:

A Materialized view (also referred to as a snapshot) is a logical structure whose data is physically stored in the database. Since data is accessed from a direct physical location, data retrieval is quicker with a materialized view than with a standard view. Let’s look at the functionality and choices for refreshing a materialized view in this blog.

Description:

What is a Materialized view ?

A materialized view, like a standard view, gets its data from a query. The results of a normal view are only temporary, they are lost once the query is completed and the query must be re-executed if they are desired again. The effects of a materialized view, on the other hand, are held and physically stored in a database object that looks like a table.

This feature ensures that the underlying query only needs to be run once, and the results are then accessible to anyone who requires them.

Refreshing Materialized views:

The two main techniques of MView (Materialized View) refresh are

1. In-place refresh : In-place refresh executes refresh statements directly on the materialized view.
2. Out-of-place refresh (new refresh option in Oracle 12cR1) : Out-of-place refresh creates one or more outside tables and executes the refresh statements on the outside tables and then switches the materialized view or affected materialized view partitions with the outside tables

In Oracle 12cR1, a new refresh method has been introduced known as synchronous refresh. In this, the user does not directly modify the contents of the base tables but must use the APIs provided by the synchronous refresh package that will apply these changes to the base tables and materialized views at the same time to ensure their consistency. The synchronous refresh method is well-suited for data warehouses.

Types of Materialized view refresh:

— Normal syntax to create a materialized view.

CREATE MATERIALIZED VIEW <Name of the MView>

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

AS

SELECT …;

In the above syntax, BUILD options are.

1. IMMEDIATE : The default option for materialized view to populate immediately.
2. DEFERRED : The materialized view is populated on the first requested refresh.

Refresh types are as follows.

1. FAST : A fast refresh is performed, if materialized view logs are not present against the source tables in advance, the creation fails. Materialized view log named as MLOG$_<base_table> and it’s located in the same schema of source database as master table. It performs refresh according to the changes occurred in master table.
2. COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
3. FORCE : A fast refresh is done, if one is not possible a complete refresh is performed.

A refresh can be triggered in one of two ways.

1. ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
2. ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

Materialized view refresh can be performed in incremental or a complete refresh.

Incremental refresh contains two methods known as log-based refresh and partition change tracking (PCT) refresh. Incremental refresh also known as FAST refresh because it usually performs faster than complete refresh.

A materialized view can be manually refreshed using the DBMS_MVIEW package.

exec dbms_mview.refresh(‘TEST_MV’);

To perform a complete refresh of materialized view,

SQL> BEGIN

dbms_mview.refresh(‘TEST_SYS.TEST_MV’, method => ‘C’);

END;

/

To compile a materialized view,

SQL> alter materialized view TEST_SYS.TEST_MV compile;

To check status of materialized view,

SQL> set echo on timin on

SQL> SELECT mview_name, staleness, last_refresh_type

FROM dba_mviews

WHERE mview_name = ‘TEST_MV’;

/

To find the query of the materialized view,

select query

from dba_mviews

where owner = ‘TEST_SYS’

and mview_name = ‘TEST_MV’

/

New features:

Since Oracle 12c, statistics are calculated on the materialized view automatically due to online statistics gathering.

Complete Refresh performance can be improved using the optional parameter atomic_refresh of the procedure dbms_mview.refresh

BEGIN

dbms_mview.refresh(‘MV_TEST, method => ‘C’, atomic_refresh => FALSE);

END;

Starting from 12c R1 , a new refresh option has been introduced which is known as “out-of-place refresh”. It works with all the existing methods of refresh like FAST “F” , COMPLETE “C” , PCT “P” , FORCE “?”

Out-of-place refresh is very useful for large amount of data changes and it enables to achieve high degree of availability during a materialized view refresh , by allowing a direct access and query rewrite when execution of refresh statements.

exec dbms_mview.refresh(‘TEST_MV’, out_of_place=>true, atomic_refresh=>false);

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. This wind-forward is based on changes computed using materialized view logs, like a conventional fast refresh, but the operation only affects the current statement. The changes are not persisted in the materialized view, so a conventional refresh is still required at some point.

Oracle 18c introduced the ability to use the ON STATEMENT refreshes of materialized views built with JSON_TABLE function calls.

Oracle 19c can perform query rewrites of statements using some SQL/JSON functions (JSON_VALUE, JSON_EXISTS) to use a materialized view containing an appropriate JSON_TABLE call.

Materialized views should explicitly be refreshed on every commit, on a periodic time schedule or typically in data warehouses, at the end of ETL job.