Manually Refreshing Materialized Views and Creating Refresh Groups in Oracle - Page 2
May 2, 2003
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');
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.