Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 2, 2003

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

By Ajay Gursahani

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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM