Basic Replication -- 7 : Refresh Groups

So far, all my blog posts in this series cover "single" Materialized Views (even if I have created two MVs, they are independent of each other and can be refreshed at different schedules).

A Refresh Group is what you would define if you want multiple MVs to be refreshed to the same point in time.  This allows for
(a) data from transaction that touch multiple tables
or
(b) views of multiple tables
to be consistent in the target MVs.

For example, if you have SALES_ORDER and LINE_ITEMS tables and the MVs on these are refreshed at different times, you might see the ORDER (Header) without the LINE_ITEMs (or, worse, in the absence of Referential Integrity constraints, LINE_ITEMs without the ORDER (Header) !).

Here's a demo, using the HR  DEPARTMENTS and EMPLOYEES table with corresponding MVs built in the HEMANT schema.

SQL> show user
USER is "HR"
SQL> select count(*) from departments;

  COUNT(*)
----------
        27

SQL> select count(*) from employees;

  COUNT(*)
----------
       107

SQL> 
SQL> grant select on departments to hemant;

Grant succeeded.

SQL> grant select on employees to hemant;

Grant succeeded.

SQL>
SQL> create materialized view log on departments;

Materialized view log created.

SQL> grant select, delete on mlog$_departments to hemant;

Grant succeeded.

SQL>
SQL> create materialized view log on employees;

Materialized view log created.

SQL> grant select, delete on mlog$_employees to hemant;

Grant succeeded.

SQL>
SQL>

Having created the source MV Logs  note that I have to grant privileges to the account (HEMANT) that will be reading and deleting from the MV Logs.

Next, I setup the MVs and the Refresh Group

SQL> show user
USER is "HEMANT"
SQL>
SQL> select count(*) from hr.departments;

  COUNT(*)
----------
        27

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

SQL>
SQL>
SQL> create materialized view mv_dept
  2  refresh fast on demand
  3  as select department_id as dept_id, department_name as dept_name
  4  from hr.departments
  5  /

Materialized view created.

SQL>
SQL> create materialized view mv_emp
  2  refresh fast on demand
  3  as select department_id as dept_id, employee_id as emp_id,
  4  first_name, last_name, hire_date
  5  from hr.employees
  6  /

Materialized view created.

SQL>
SQL> select count(*) from mv_dept;

  COUNT(*)
----------
        27

SQL> select count(*) from mv_emp;

  COUNT(*)
----------
       107

SQL>
SQL> execute dbms_refresh.make(-
> name=>'HR_MVs',-
> list=>'MV_DEPT,MV_EMP',-
> next_date=>sysdate+0.5,-
> interval=>'sysdate+1');

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>

Here, I have built two MVs and then a Refresh Group called "HR_MVS".  The first refresh will be 12hours from now and every subsequent refresh will be after 24hours.  (The Refresh Interval must be set to what would be larger than the time taken to execute the actual Refresh).

However, I can manually execute the Refresh after new rows are populated into the source tables. First, I insert new rows

SQL> show user
USER is "HR"
SQL> insert into departments (department_id, department_name)
  2  values
  3  (departments_seq.nextval, 'New Department');

1 row created.

SQL> select department_id
  2  from departments
  3  where department_name = 'New Department';

DEPARTMENT_ID
-------------
          280

SQL> insert into employees(employee_id, first_name, last_name, email, hire_date, job_id, department_id)
  2  values
  3  (employees_seq.nextval, 'Hemant', 'Chitale', 'hkc@myenterprise.com', sysdate, 'AD_VP', 280);

1 row created.

SQL> select employee_id
  2  from employees
  3  where first_name = 'Hemant';

EMPLOYEE_ID
-----------
        208

SQL> commit;

Commit complete.

SQL>

Now that there are new rows, the target MVs must be refreshed together.

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> execute dbms_refresh.refresh('HR_MVS');

PL/SQL procedure successfully completed.

SQL> select count(*) from mv_dept;

  COUNT(*)
----------
        28

SQL> select count(*) from mv_emp;

  COUNT(*)
----------
       108

SQL>
SQL> select * from mv_dept
  2  where dept_id=280;

   DEPT_ID DEPT_NAME
---------- ------------------------------
       280 New Department

SQL> select * from mv_emp
  2  where emp_id=208;

   DEPT_ID     EMP_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
---------- ---------- -------------------- ------------------------- ---------
       280        208 Hemant               Chitale                   12-OCT-19

SQL>

Both MVs have been Refresh'd together as an ATOMIC Transaction.  If either of the two MVs had failed to refresh (e.g. unable to allocate extent to grow the MV), both the INSERTs would be rolled back.  (Note : It is not a necessary requirement that both source tables have new / updated rows, the Refresh Group works even if only one of the two tables has new / updated rows).

Note : I have used DBMS_REFRESH.REFRESH (instead of DBMS_MVIEW.REFRESH) to execute the Refresh.

You can build multiple Refresh Groups, each consisting of *multiple* Source Tables from the same source database.
You would define each Refresh Group to maintain consistency of data across multiple MVs (sourced from different tables).
Besides the Refresh Group on two HR tables, I could have, within the HEMANT schema, more Refresh Groups on FINANCE schema tables as well.

(Can you have a Refresh Group sourcing from tables from different schemas ?  Try that out !)


What's the downside of Refresh Groups ?    
Undo and Redo !  Every Refresh consists of INSERT/UPDATE/DELETE operations on the MVs.  And if any one of the MVs fails to Refresh, the entire set of DMLs (across all the MVs in the Refresh Group) has to *Rollback* !