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;
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
/
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
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
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
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.
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 secsPart 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
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
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
/
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
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;
/
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.
@go-faster.co.uk on BlueSky
Posts
