dcsimg

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;

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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers