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