Manually Refreshing Materialized Views and Creating Refresh Groups in Oracle

Friday May 2nd 2003 by Ajay Gursahani
Share:

Learn how to manually refresh materialized views and create refresh groups using Oracle supplied packages.

You can perform manual refreshes in addition to automatic refreshes as explained in my earlier article (Materialized Views). Oracle supplies DBMS_SNAPSHOT and DBMS_MVIEW packages, which we can use to refresh materialized views / snapshots.



DBMS_SNAPSHOT



SQL> execute DBMS_SNAPSHOT.REFRESH( 'MV_EMP','f');
PL/SQL procedure successfully completed.

Parameters of Procedure REFRESH

The first parameter to the procedure REFRESH is the name of the materialized view or snapshot, the second parameter specifies the type of refresh.

Type of Refresh Description
F, f Fast Refresh
C, c Complete Refresh
A Always perform complete refresh
? Use the default option

The manual refresh overtakes any previous refresh timing options, which were specified during the creation of the view. It more specifically overrides the 'start with' clause, which is specified with the 'create materialized view' command.

Also provided with DBMS_SNAPSHOT is the REFRESH_ALL procedure. This procedure refreshes all materialized views, which were defined using the automatic refreshes.

SQL> execute DBMS_SNAPSHOT.REFRESH_ALL;
PL/SQL procedure successfully completed.

Parameters of procedure REFRESH_ALL

The REFRESH_ALL procedure does not accept any parameters.

REFRESH GROUPS - CLUBBING RELATED VIEWS

Oracle provides the means by which you can group related views together. Oracle supplies the DBMS_REFRESH package with the following procedures;

MAKE Make a Refresh Group
ADD Add materialized view to the refresh group
SUBTRACT Remove materialized view from the refresh group
REFRESH Manually refresh the group
CHANGE Change refresh interval of the refresh group
DESTROY Remove all materialized views from the refresh group and delete the refresh group

DBMS_REFRESH - Procedure MAKE

The MAKE procedure is used to create a new Refresh group.

We will make a refresh group my_group_1:

SQL> execute DBMS_REFRESH.MAKE(
	name => 'my_group_1',
	list => ' mv_market_rate, mv_dealer_rate',
	next_date => sysdate,
	interval => 'sysdate+1/48');

my_group_1 has two views in its group, mv_market_rate and mv_dealer_rate. Both of these views will be refreshed at an interval of 30 minutes

DBMS_REFRESH - Procedure ADD

Add a snapshot/materialized view to the already existing refresh group:

SQL> execute DBMS_REFRESH.ADD(
	name => 'my_group_1',
	list => 'mv_borrowing_rate');

my_group_1 now has three views in its group, mv_market_rate, mv_dealer_rate and mv_borrowing_rate ( the newly added view). All of these views will be refreshed at an interval of 30 minutes

DBMS_REFRESH - Procedure SUBTRACT

Removes a snapshot/materialized view from the already existing refresh group.

SQL> execute DBMS_REFRESH.SUBTRACT(
	name => 'my_group_1',
	list => 'mv_market_rate');

my_group_1 now has two views in its group, mv_dealer_rate and mv_borrowing_rate. We have removed mv_market_rate from the refresh group, my_group_1.

DBMS_REFRESH - Procedure REFRESH

Manually refreshes the already existing refresh group.

SQL> execute DBMS_REFRESH.REFRESH(
	name => 'my_group_1');

DBMS_REFRESH - Procedure CHANGE

The CHANGE procedure is used to change the refresh interval of the refresh group.

SQL> execute DBMS_REFRESH.CHANGE(
	name => 'my_group_1',
	next_date => NULL,
	interval => 'sysdate+1/96');

The views in my_group_1 will now be refreshed at an interval of 15 minutes.

DBMS_REFRESH - Procedure DESTROY

Removes all materialized views from the refresh group and deletes the refresh group.

SQL> execute DBMS_REFRESH.DESTROY(
	name => 'my_group_1');

Summary

Creating a refresh group helps to club all related views together and thus refreshes them together. Manual refresh gives us an opportunity to override the automatic refresh settings.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved