Materialized View Refresh Group

Refresh Group: A refresh group is a collection of Materialized Views. Why do you need a refresh group? To maintain the database consistency, we may need to refresh more than  one Materialized View at a same time in a single transaction. This can be achieved using a refresh group. Another purpose of a group is the keep all the similar MViews together and keep the database simple and clean.

Oracle supplies a DBMS_REFRESH built in package which lets you create a refresh group, add/delete MView, groups and much more which we will discuss with example. We will create a refresh group, add MViews to a refresh group,  refresh the group and then cleanup refresh group. 

Assumption: A user running below scripts have the ability to create database objects. The schema SCOTT has two complete materialized views mview1 and mview2,

CREATE REFRESH GROUP: The procedure MAKE creates a refresh group. You will need the group name, list of MViews, next run time and the refresh interval.
SYNTAX:

BEGIN
 DBMS_REFRESH.MAKE(name => 'scott_refresh_group',
 list => 'mview1,mview2',
 next_date => sysdate,
 interval => 'sysdate+ 1') ;
END;
/
PL/SQL procedure successfully completed.

scott_refresh_group has two materialized views named mview1 and mview2. The refresh happens as soon as the group is created and it is scheduled refreshes daily going forward.

ADD MVIEW: There is a new materialized view which needs to be a part of this group. Procedure ADD lets your add to existing group.  We are now going to add mview2 to scott_refresh_group:

SYNTAX:

BEGIN
 DBMS_REFRESH.ADD( name => 'scott_refresh_group',
 list => 'mview3') ;
END;
/
PL/SQL procedure successfully completed.

Well the PL/SQL says it has been added, we will need to verify if mview3 is added or not using system view.

Query: To check Mview3

SELECT owner,
 name,
 rname,
 refgroup,
 next_date,
 interval
FROM all_refresh_children
WHERE rname = 'SCOTT_REFRESH_GROUP';

DELETE MVIEW: The team identified the mview3 just added is not adding any benefit therefore they are going to drop it. It is best to remove from a group before dropping a view. The procedure SUBTRACT removes materialized view from the group.

SYNTAX:

BEGIN
 DBMS_REFRESH.SUBTRACT( name => 'scott_refresh_group',
 list => 'mview3') ;
END;
/
PL/SQL procedure successfully completed.

Query: To check Delete mview3

SELECT owner,
 name,
 rname,
 refgroup,
 next_date,
 interval
FROM all_refresh_children
WHERE rname = 'SCOTT_REFRESH_GROUP';

REFRESH GROUP: We have added/deleted MView from a group. The refresh is scheduled and the frequency is set up while creating a refresh group.  Sometime, we may need to refresh the group manually for various reasons.

MANUAL REFRESH SYNTAX: 

BEGIN
 DBMS_REFRESH.REFRESH(name => 'SCOTT_REFRESH_GROUP') ;
END;
/
PL/SQL procedure successfully completed.

CHANGE: The refresh group is scheduled to run during off hours when the transactions volumes are low. The team who owns the group identified the problem and wanted to request a change in the frequency one a day to twice a day.
CHANGE SYNTAX:

BEGIN
 DBMS_REFRESH.CHANGE( name => 'SCOTT_REFRESH_GROUP',
 next_date => NULL,
 interval => 'sysdate+1/2') ;
END;
/ 
PL/SQL procedure successfully completed.

DESTROY: The DESTROY procedure drop a refresh group but it does not drop materialized views under the group.

DESTROY SYNTAX:

BEGIN
 DBMS_REFRESH.DESTROY( name => 'SCOTT_REFRESH_GROUP');
END;
/
PL/SQL procedure successfully completed..

Query: To check Refresh Group

SELECT rowner,
 rname,
 refgroup,
 next_date,
 interval
FROM dba_refresh
WHERE rname = 'SCOTT_REFRESH_GROUP';

No Result:
Query: To check Mviews

SELECT owner,
 mvvie_name
FROM dba_mviews
WHERE mview_name IN ('MVIEW1', 'MVIEW2');

Useful Refresh Group System Views:

DBA_RGROUP, DBA_REFRESH, USER_REFRESH, ALL_REFRESH_CHILDREN, USER_REFRESH_CHILDREN, DBA_REFRESH_CHILDREN.

DBA_RGPOUP and DBA_REFRESH display all the refresh group and DBA_REFRESH_CHILDREN displays all the children for specific refresh group.

Now, I want you to check if you have materialized views which are great candidate for refresh group.  If you have, you know what to do next?