recent

Titulo

Materialized View Refresh Group

Refresh Group: A refresh group is a collection of Materialized Views. Why do you need a refresh group? To maintain the database consistency, we may need to refresh more than  one Materialized View at a same time in a single transaction. This can be achieved using a refresh group. Another purpose of a group is the keep all the similar MViews together and keep the database simple and clean.

Oracle supplies a DBMS_REFRESH built in package which lets you create a refresh group, add/delete MView, groups and much more which we will discuss with example. We will create a refresh group, add MViews to a refresh group,  refresh the group and then cleanup refresh group. 

Assumption: A user running below scripts have the ability to create database objects. The schema SCOTT has two complete materialized views mview1 and mview2,

CREATE REFRESH GROUP: The procedure MAKE creates a refresh group. You will need the group name, list of MViews, next run time and the refresh interval.
SYNTAX:
BEGIN
 DBMS_REFRESH.MAKE(name => 'scott_refresh_group',
 list => 'mview1,mview2',
 next_date => sysdate,
 interval => 'sysdate+ 1') ;
END;
/
PL/SQL procedure successfully completed.
scott_refresh_group has two materialized views named mview1 and mview2. The refresh happens as soon as the group is created and it is scheduled refreshes daily going forward.

ADD MVIEW: There is a new materialized view which needs to be a part of this group. Procedure ADD lets your add to existing group.  We are now going to add mview2 to scott_refresh_group:
SYNTAX:
BEGIN
 DBMS_REFRESH.ADD( name => 'scott_refresh_group',
 list => 'mview3') ;
END;
/
PL/SQL procedure successfully completed.
Well the PL/SQL says it has been added, we will need to verify if mview3 is added or not using system view.
Query: To check Mview3
SELECT owner,
 name,
 rname,
 refgroup,
 next_date,
 interval
FROM all_refresh_children
WHERE rname = 'SCOTT_REFRESH_GROUP';

DELETE MVIEW: The team identified the mview3 just added is not adding any benefit therefore they are going to drop it. It is best to remove from a group before dropping a view. The procedure SUBTRACT removes materialized view from the group.
SYNTAX:
BEGIN
 DBMS_REFRESH.SUBTRACT( name => 'scott_refresh_group',
 list => 'mview3') ;
END;
/
PL/SQL procedure successfully completed.
Query: To check Delete mview3
SELECT owner,
 name,
 rname,
 refgroup,
 next_date,
 interval
FROM all_refresh_children
WHERE rname = 'SCOTT_REFRESH_GROUP';

REFRESH GROUP: We have added/deleted MView from a group. The refresh is scheduled and the frequency is set up while creating a refresh group.  Sometime, we may need to refresh the group manually for various reasons.
MANUAL REFRESH SYNTAX: 
BEGIN
 DBMS_REFRESH.REFRESH(name => 'SCOTT_REFRESH_GROUP') ;
END;
/
PL/SQL procedure successfully completed.
CHANGE: The refresh group is scheduled to run during off hours when the transactions volumes are low. The team who owns the group identified the problem and wanted to request a change in the frequency one a day to twice a day.
CHANGE SYNTAX:
BEGIN
 DBMS_REFRESH.CHANGE( name => 'SCOTT_REFRESH_GROUP',
 next_date => NULL,
 interval => 'sysdate+1/2') ;
END;
/ 
PL/SQL procedure successfully completed.

DESTROY: The DESTROY procedure drop a refresh group but it does not drop materialized views under the group.
DESTROY SYNTAX:
BEGIN
 DBMS_REFRESH.DESTROY( name => 'SCOTT_REFRESH_GROUP');
END;
/
PL/SQL procedure successfully completed..
Query: To check Refresh Group
SELECT rowner,
 rname,
 refgroup,
 next_date,
 interval
FROM dba_refresh
WHERE rname = 'SCOTT_REFRESH_GROUP';
No Result:
Query: To check Mviews
SELECT owner,
 mvvie_name
FROM dba_mviews
WHERE mview_name IN ('MVIEW1', 'MVIEW2');

Useful Refresh Group System Views:
DBA_RGROUP, DBA_REFRESH, USER_REFRESH, ALL_REFRESH_CHILDREN, USER_REFRESH_CHILDREN, DBA_REFRESH_CHILDREN.

DBA_RGPOUP and DBA_REFRESH display all the refresh group and DBA_REFRESH_CHILDREN displays all the children for specific refresh group.

Now, I want you to check if you have materialized views which are great candidate for refresh group.  If you have, you know what to do next? 

Interested in working with me? I can be reached at pbaniya04[at]gmail.com for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

5 comments

  1. I need to extract refresh group definitions in order to copy them to another database. They are very old and nobody have the original scripts! Any idea? Thanks

    ReplyDelete
  2. alepw , i m looking for the same thing . For the past 2 days i ve search but found nothing , if you find something please let me know. I will do the same.

    ReplyDelete
  3. 1) First, list all the mviews under a refresh group.
    2) Use dbms_metadata.Get_ddl to generate DDL for all the materialized views listed on refresh group. (http://www.dbarepublic.com/2014/03/dbmsmetadata-get-ddl.html).
    3) Run your DDL scripts from step 2 to your new database.
    4) Use Oracle Enterprise manager to generate DDL for a refresh group.
    5) Run DDL captured on step 4 to your new database.

    Your DBA has access to enterprise manager tool to get DDL that you need,If they can't provide the DDL for you, you should be able to create your own which should not take much as I have explained in this article. I hope this helps. Good Luck both!

    ReplyDelete
  4. To refresh a M View, we could use DBMS_MVIEW.REFRESH package which doesnt need a refresh group. This could help you

    ReplyDelete
  5. Greetings!
    How do I create a refresh group without create a job (not a Schedule job).
    Tks!

    ReplyDelete

Powered by Blogger.