My Oracle Support Banner

How to Refresh a Materialized View in Parallel (Doc ID 577870.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.8 and later
Information in this document applies to any platform.

Purpose

How to Refresh a Materialized View in Parallel


Materialized views provide performance benefits to data warehouse applications. Some OLTP applications also benefit from materialized views involving non-volatile data. Oracle provides flexible ways to refresh materialized views: you can refresh them full or incremental; you can refresh them on demand or at the commit time in the source table. When the size of the materialized view grows, one needs to explore ways to perform the refresh faster. One of the ways to expedite the refresh is to use parallel execution.

Scope

Refreshing a Materialized View


The attributes related to refreshing a materialized view are specified at the creation time of the materialized view (or later through the ALTER MATERIALIZED VIEW statement).

The two most important attributes that impact the refresh time are:

 Refresh FAST or COMPLETE
 Refresh ON COMMIT or ON DEMAND

A FAST refresh means an incremental refresh, which indicates that the materialized view will be refreshed based on only the data changes that have occurred in the master table(s).  A COMPLETE refresh indicates that the materialized view will be refreshed by re-executing the query defining the materialized view.

The ON COMMIT refresh indicates that the materialized view will be whenever the data in the master table changes. The ON DEMAND refresh indicates that the materialized view will be refreshed on demand by explicitly executing one of the REFRESH procedures in the DBMS_MVIEW package.

In this article, we do not specify when the materialized view will be refreshed, nor how it will be refreshed, which then defaults to COMPLETE (since we have no materialized view log) and ON DEMAND.  However, the concepts discussed here will be applicable to all refresh methods.  

In this article, we will use the following materialized view to illustrate the various parallel refresh mechanisms.

CREATE MATERIALIZED VIEW MV_PART_SALES 
AS 
SELECT PART_ID, SALE_DATE, SUM(QUANTITY) 
FROM SALES_HISTORY 
GROUP BY PART_ID, SALE_DATE;

 

SQL> desc SALES_HISTORY 

Name Null? Type  
----------------------------------------- -------- ------------------  
PART_ID          NOT NULL VARCHAR2(50)  
STORE_ID       NOT NULL VARCHAR2(50)  
SALE_DATE    NOT NULL DATE  
QUANTITY     NOT NULL NUMBER(10,2)

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
 How to Refresh a Materialized View in Parallel
Scope
 Refreshing a Materialized View
Details
 Parallel Execution
 1. The PARALLELISM Parameter of the DBMS_MVIEW.REFRESH Procedure
 2. PARALLEL Attribute of the Materialized View
 3. PARALLEL Attribute of the Master Table
 4. PARALLEL Hint in the Query Defining the Materialized View
 Conclusion

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.