Refreshing Materialized Views with Referential Integrity Constraints

I have a number of tables on a reporting system which have referential integrity constraints, and whose contents are replicated from a primary system. I am going to create materialized views on these prebuilt tables to manage incremental refresh. However, the referential integrity means that some materialized view will have to be refreshed before others which refer to them.
The referential constraints can be queried from the DBA_/USER_CONSTRAINTS view. Clearly this defines a hierarchy of tables. I could construct a hierarchical query on this data I could determine the order in which to refresh the materialized views.  Naturally, I looked for someone who had done this already and I found this on the Ask Tom website: All Parent - Child tables in the database. It was written in 2001 for Oracle 8.1.6. I have used the same demonstration, and enhanced it some newer features. I ran my tests on 12c.

create table p ( x int primary key ); 
create materialized view log on p;
create table c0 ( x int primary key); 
create table c1 ( x primary key constraint c1_p  references p); 
create table c2 ( x primary key constraint c2_c1 references c1); 
create table c3 ( x primary key constraint c3_c2 references c2); 
create table c4 ( x primary key constraint c4_c2 references c2); 

create materialized view c0 on prebuilt table as select x from p;
create materialized view c1 on prebuilt table as select x from p;
create materialized view c2 on prebuilt table as select x from p;
create materialized view c3 on prebuilt table as select x from p;
create materialized view c4 on prebuilt table as select x from p;

insert into p select rownum from dual connect by level <= 42;
commit;

I have no problem refreshing a materialized view C0 without an referential constraints, but C4 has a constraint that refers to C2 that has not been refreshed so I get a parent key not found error.

exec dbms_mview.refresh('C0',method=>'F');
exec dbms_mview.refresh('C4',method=>'F');

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02291: integrity constraint (SCOTT.C4_C2) violated - parent key not found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1

Part 1: Incremental Refresh 

I will start with the easier problem of incremental refresh. Related materialized views can be passed as a list to the DBMS_MVIEW.REFRESH procedure and will be refreshed in a single database transaction. Hierarchies of related tables are created by the aggregation of these constraints. I can calculate which materialized views to group together.
Let's start by creating a working storage table that contains a row for each materialized view.

DROP TABLE dmk_mview_refresh PURGE
/
CREATE TABLE dmk_mview_refresh AS 
SELECT owner, mview_name, mview_name refresh_group
FROM   dba_mviews
WHERE  owner = user
/

Ideally, I would like to be able to refresh each one independently. If I were using refresh groups, each materialized view would be in its own group, which for simplicity will named the same as the materialized view.

set pages 99 lines 200
column owner format a10
column mview_name format a10
column refresh_Group format a10
break on report
SELECT * FROM dmk_mview_refresh
ORDER BY 1,2,3;

OWNER      MVIEW_NAME REFRESH_GR
---------- ---------- ----------
SCOTT      C0         C0
SCOTT      C1         C1
SCOTT      C2         C2
SCOTT      C3         C3
SCOTT      C4         C4

This PL/SQL block works through each referential constraint that links any two materialized views, and determines the current refresh group for each materialized view and if they are not in the same group it moves all materialized views in the group of referring materialized view to the group of the referred to materialized view.

set serveroutput on
DECLARE
  l_owner VARCHAR2(30) := user;
  l_groupc VARCHAR2(30);
  l_groupr VARCHAR2(30);
BEGIN
  FOR i IN (
select mr.owner ownerr, mr.mview_name mview_namer
, r.constraint_name, r.status
,  mc.owner ownerc, mc.mview_name mview_namec
from dba_mviews mc
, dba_constraints r
, dba_constraints c
, dba_mviews mr
where mc.owner = l_owner
and r.owner = mc.owner
and r.table_name = mc.container_name
and r.constraint_type = 'R'
and c.owner = r.r_owner
and c.constraint_name = r.r_constraint_name
and mr.owner = c.owner
and mr.container_name = c.table_name
  ) LOOP
    dbms_output.put_line(i.ownerr||'.'||i.mview_namer||'->'||i.ownerc||'.'||i.mview_namec||' constraint '||i.constraint_name);

    SELECT refresh_group
    INTO   l_groupr
    FROM   dmk_mview_refresh
    WHERE  owner = i.ownerr
    AND    mview_name = i.mview_namer;

    SELECT refresh_group
    INTO   l_groupc
    FROM   dmk_mview_refresh
    WHERE  owner = i.ownerc
    AND    mview_name = i.mview_namec;

    IF l_groupc != l_groupr THEN
      UPDATE dmk_mview_refresh
      SET    refresh_group = l_groupc
      WHERE  refresh_group = l_groupr;

      dbms_output.put_line('Update '||l_groupr||'->'||l_groupc||' '||SQL%rowcount||' rows updated');
    END IF;

  END LOOP;
END;
/

SCOTT.C2->SCOTT.C4 constraint C4_C2
Update C2->C4 1 rows updated
SCOTT.C2->SCOTT.C3 constraint C3_C2
Update C4->C3 2 rows updated
SCOTT.C1->SCOTT.C2 constraint C2_C1
Update C1->C3 1 rows updated

You can see that C1, C2, C3 and C4 are now all in group C3. So they need to be refreshed in a single operation.

break on refresh_group skip 1
select * from dmk_mview_refresh
order by 3,1,2;

OWNER      MVIEW_NAME REFRESH_GR
---------- ---------- ----------
SCOTT      C0         C0

SCOTT      C1         C3
SCOTT      C2
SCOTT      C3
SCOTT      C4

I can use the LISTAGG() analytic function to simply generate the list and pass it to DBMS_MVIEW.REFRESH.

DECLARE
  l_t1 TIMESTAMP;
  l_t2 TIMESTAMP;
  l_tdiff NUMBER;
  l_module VARCHAR2(64);
  l_action VARCHAR2(64);
BEGIN 
  dbms_application_info.read_module(l_module,l_action);
  dbms_application_info.set_module('MV Group Refresh','Begin');
  FOR i IN (
select  refresh_group, listAgg(owner||'.'||mview_name, ',') WITHIN GROUP (ORDER BY mview_name) mview_list
from dmk_mview_refresh
GROUP BY refresh_Group
  ) LOOP
    dbms_application_info.set_action(i.refresh_group);
    l_t1 := SYSTIMESTAMP;

    dbms_output.put_line(l_t1||' Start Refresh MV group: '||i.mview_list);
    dbms_mview.refresh(i.mview_list,method=>'F');

    l_t2 := SYSTIMESTAMP;
    l_tdiff := 60*(60*(24*extract(day from l_t2-l_t1)+extract(hour from l_t2-l_t1))+extract(minute from l_t2-l_t1))+extract(second from l_t2-l_t1);
    dbms_output.put_line(l_t2||' End Refresh MV '||i.mview_list||':'||l_tdiff||' secs');
    commit;
  END LOOP;
  dbms_application_info.set_module(l_module,l_action);END;
/

PL/SQL procedure successfully completed.

And you can see they materialized views refresh without error.

20-OCT-16 09.50.43.119765 Start Refresh MV group: SCOTT.C0
20-OCT-16 09.50.43.382501 End Refresh MV SCOTT.C0:.262736 secs
20-OCT-16 09.50.43.383195 Start Refresh MV group: SCOTT.C1,SCOTT.C2,SCOTT.C3,SCOTT.C4
20-OCT-16 09.50.43.655243 End Refresh MV SCOTT.C1,SCOTT.C2,SCOTT.C3,SCOTT.C4:.272048 secs

If I wanted to schedule the refresh I could also create corresponding refresh groups with DBMS_REFRESH.

Part 2: Non-Atomic Full Refresh

The second question is how to fully refresh the materialized views. I will have to do this at least once when I instantiate the replication.  I have a real-world case where there is a large volume of data in these tables, so I also want to use non-atomic refresh to reduce the time taken to refresh, and the size of the final table (see also Complete Refresh of Materialized Views: Atomic, Non-Atomic and Out-of-Place). I cannot use the same list approach as above. I can disable constraints to facilitate the refresh, but I won't be able to re-enable them until the data is integral. Of course I could disable all the constraints, refresh all the materialized views and then re-enable all the constraints.

ALTER TABLE c4 MODIFY CONSTRAINT c4_c2 DISABLE;
exec dbms_mview.refresh('C4',method=>'C',atomic_refresh=>FALSE);
ALTER TABLE c4 MODIFY CONSTRAINT c4_c2 ENABLE;
                                 *
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.C4_C2) - parent keys not found

So in this case I need to refresh the materialized views on which C4 depends first, and so on.

column mview_name format a10
column table_name format a10
column constraint_name format a10
select mr.owner, mr.mview_name, r.table_name
, r.constraint_name, r.status
,  mc.owner, mc.mview_name, c.table_name
from dba_mviews mr
, dba_constraints r
, dba_constraints c
, dba_mviews mc
where mr.owner = user
and mr.owner = r.owner
and mr.container_name = r.table_name
and r.constraint_type = 'R'
and c.owner = r.r_owner
and c.constraint_name = r.r_constraint_name
and mc.owner = c.owner
and mc.container_name = c.table_name
order by 1,2

OWNER      MVIEW_NAME TABLE_NAME CONSTRAINT STATUS   OWNER      MVIEW_NAME TABLE_NAME
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
SCOTT      C2         C2         C2_C1      ENABLED  SCOTT      C1         C1
SCOTT      C3         C3         C3_C2      ENABLED  SCOTT      C2         C2
SCOTT      C4         C4         C4_C2      ENABLED  SCOTT      C2         C2

I need to tree walk the hierarchy of foreign keys to determine which tables need to be refreshed first.

column path format a20
with x as (
select /*+MATERIALIZE*/ mr.owner ownerr, mr.mview_name mview_namer, r.table_name table_namer
, r.constraint_name, r.status
,  mc.owner ownerc, mc.mview_name mview_namec, c.table_name table_namec
from dba_mviews mc
, dba_constraints r
, dba_constraints c
, dba_mviews mr
where mr.owner = user
and mr.owner = r.owner
and mr.container_name = r.table_name
and r.constraint_type = 'R'
and c.owner = r.r_owner
and c.constraint_name = r.r_constraint_name
and mc.owner = c.owner
and mc.container_name = c.table_name
)
select level mylevel, ownerr, mview_namer, ownerc, mview_namec
,       sys_connect_by_path(mview_namer,'/') path
from x
connect by nocycle prior mview_namer = mview_namec
              and  prior ownerr      = ownerc
order by 1,2,3
/

I need to start with the tables with the lowest maximum level first.

   MYLEVEL OWNERR     MVIEW_NAMER OWNERC     MVIEW_NAMEC PATH
---------- ---------- ----------- ---------- ----------- --------------------
         1 SCOTT      C2          SCOTT      C1          /C2
         1 SCOTT      C3          SCOTT      C2          /C3
         1 SCOTT      C4          SCOTT      C2          /C4
         2 SCOTT      C3          SCOTT      C2          /C2/C3
         2 SCOTT      C4          SCOTT      C2          /C2/C4

Any materialized view not picked up in the tree walk is given level 0, and now I can calculate the refresh order in a single SQL statement and execute the refreshes in a single PL/SQL block.

set serveroutput on 
DECLARE
  l_t1 TIMESTAMP;
  l_t2 TIMESTAMP;
  l_tdiff NUMBER;
  l_module VARCHAR2(64);
  l_action VARCHAR2(64);
  l_sql CLOB;
BEGIN 
  dbms_application_info.read_module(l_module,l_action);
  dbms_application_info.set_module('MV Group Refresh','Begin');
  FOR i IN (
with x as ( /*obtain constraints and identify table in referred constraint*/
select /*+MATERIALIZE*/ mr.owner ownerr, mr.mview_name mview_namer, r.table_name table_namer
, r.constraint_name, r.status
,  mc.owner ownerc, mc.mview_name mview_namec, c.table_name table_namec
from dba_mviews mc
, dba_constraints r
, dba_constraints c
, dba_mviews mr
where mr.owner = user
and mr.owner = r.owner
and mr.container_name = r.table_name
and r.constraint_type = 'R'
and c.owner = r.r_owner
and c.constraint_name = r.r_constraint_name
and mc.owner = c.owner
and mc.container_name = c.table_name
), y as ( /*tree walk constraints*/
select level mylevel, ownerr, mview_namer
from x
connect by nocycle prior mview_namer = mview_namec
              and  prior ownerr      = ownerc
union /*add all MVs at level 0*/
select 0, owner, mview_name
from  dba_mviews
where owner = user
)
select  ownerr owner, mview_namer mview_name
,  max(mylevel) mview_level
from y
group by ownerr, mview_namer
order by mview_level
  ) LOOP
    dbms_application_info.set_action(i.mview_name);

    FOR j IN ( /*disable enabled constraints*/
      SELECT r.owner, r.table_name, r.constraint_name
      FROM   dba_constraints c
      ,      dba_constraints r
      WHERE  c.owner = i.owner
      AND    c.table_name = i.mview_name
      AND    c.constraint_Type IN ('U','P')
      and    r.constraint_type = 'R'
      and    c.owner = r.r_owner
      and    c.constraint_name = r.r_constraint_name
      and    r.status = 'ENABLED'
    ) LOOP
      dbms_application_info.set_action('Disable '||j.constraint_name);
      l_sql := 'ALTER TABLE '||user||'.'||j.table_name||' MODIFY CONSTRAINT '||j.constraint_name||' DISABLE';
      dbms_output.put_line(l_sql);
      EXECUTE IMMEDIATE l_sql;
    END LOOP;

    l_t1 := SYSTIMESTAMP;
    dbms_output.put_line(l_t1||' Start Refresh MV '||i.mview_name||' ('||i.mview_level||')');

    dbms_mview.refresh(i.mview_name,method=>'C',atomic_refresh=>FALSE);
    l_t2 := SYSTIMESTAMP;
    l_tdiff := 60*(60*(24*extract(day from l_t2-l_t1)+extract(hour from l_t2-l_t1))+extract(minute from l_t2-l_t1))+extract(second from l_t2-l_t1);
    dbms_output.put_line(l_t2||' End Refresh MV '||i.mview_name||':'||l_tdiff||' secs');
    commit;
    
    FOR j IN ( /*reenable disabled constraints*/
      SELECT r.owner, r.table_name, r.constraint_name
      FROM   dba_constraints c
      ,      dba_constraints r
      WHERE  c.owner = i.owner
      AND    c.table_name = i.mview_name
      AND    c.constraint_Type IN ('U','P')
      and    r.constraint_type = 'R'
      and    c.owner = r.r_owner
      and    c.constraint_name = r.r_constraint_name
      and    r.status = 'DISABLED'
    ) LOOP
      dbms_application_info.set_action('Disable '||j.constraint_name);
      l_sql := 'ALTER TABLE '||user||'.'||j.table_name||' MODIFY CONSTRAINT '||j.constraint_name||' ENABLE';
      dbms_output.put_line(l_sql);
      EXECUTE IMMEDIATE l_sql;
    END LOOP;

  END LOOP;
  dbms_application_info.set_module(l_module,l_action);
END;
/

Constraints and disabled before the refresh and re-enabled afterwards. The maximum tree level of the materialized view is shown in brackets.

20-OCT-16 10.32.21.985520 Start Refresh MV C0 (0)
20-OCT-16 10.32.22.497711 End Refresh MV C0:.512191 secs
ALTER TABLE SCOTT.C2 MODIFY CONSTRAINT C2_C1 DISABLE
20-OCT-16 10.32.22.728865 Start Refresh MV C1 (0)
20-OCT-16 10.32.23.174410 End Refresh MV C1:.445545 secs
ALTER TABLE SCOTT.C2 MODIFY CONSTRAINT C2_C1 ENABLE
ALTER TABLE SCOTT.C3 MODIFY CONSTRAINT C3_C2 DISABLE
ALTER TABLE SCOTT.C4 MODIFY CONSTRAINT C4_C2 DISABLE
20-OCT-16 10.32.23.437297 Start Refresh MV C2 (1)
20-OCT-16 10.32.23.873448 End Refresh MV C2:.436151 secs
ALTER TABLE SCOTT.C3 MODIFY CONSTRAINT C3_C2 ENABLE
ALTER TABLE SCOTT.C4 MODIFY CONSTRAINT C4_C2 ENABLE
20-OCT-16 10.32.24.240742 Start Refresh MV C3 (2)
20-OCT-16 10.32.24.714952 End Refresh MV C3:.47421 secs
20-OCT-16 10.32.25.007517 Start Refresh MV C4 (2)
20-OCT-16 10.32.25.408507 End Refresh MV C4:.40099 secs

PL/SQL procedure successfully completed.