Manually Refreshing Materialized Views and Creating Refresh Groups in Oracle - Page 2

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;

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.