Manually Refreshing Materialized Views And Creating Refresh Groups In Oracle

Manually Refreshing Materialized Views and Creating Refresh Groups in Oracle Ajay Gursahani, [email protected] 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.