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.