Hi,
When I have 2 materialized views (MV_1A and MV_1B) that are based on the same materialized view (MV_1) then using dbms_mview.refresh with the nested=>true option on 1 of those materialized views invalidates the other materialized view. As I understand it, the nested=>true option always refreshes the dependent materialized views
even if they are already fresh. This in turn causes the other materialized view to change its staleness state from FRESH to NEEDS_COMPILE.
Example code:
SQL> create table TABLE_A (
common_id NUMBER(6),
value_a NUMBER(12)
);
Table TABLE_A created.
SQL> create table TABLE_B (
common_id NUMBER(6),
value_b NUMBER(12)
);
Table TABLE_B created.
SQL> create materialized view MV_1 as
(
select common_id, value_a, value_b
from table_a
join table_b using (common_id)
);
Materialized view MV_1 created.
SQL> create materialized view MV_1A as
(
select common_id, value_a + 1, value_b - 1
from mv_1
);
Materialized view MV_1A created.
SQL> create materialized view MV_1B as
(
select common_id, value_a + 2, value_b - 2
from mv_1
);
Materialized view MV_1B created.
SQL> select mview_name, staleness from user_mviews where mview_name in ('MV_1', 'MV_1A', 'MV_1B') order by mview_name;
MVIEW_NAME
--------------------------------------------------------------------------------
STALENESS
-------------------
MV_1
FRESH
MV_1A
FRESH
MV_1B
FRESH
SQL> insert into table_a values (1, 100);
1 row inserted.
SQL> commit;
Commit complete.
SQL> select mview_name, staleness from user_mviews where mview_name in ('MV_1', 'MV_1A', 'MV_1B') order by mview_name;
MVIEW_NAME
--------------------------------------------------------------------------------
STALENESS
-------------------
MV_1
NEEDS_COMPILE
MV_1A
FRESH
MV_1B
FRESH
SQL> execute DBMS_MVIEW.REFRESH( 'MV_1A', nested => TRUE );
PL/SQL procedure successfully completed.
SQL> select mview_name, staleness from user_mviews where mview_name in ('MV_1', 'MV_1A', 'MV_1B') order by mview_name;
MVIEW_NAME
--------------------------------------------------------------------------------
STALENESS
-------------------
MV_1
FRESH
MV_1A
FRESH
MV_1B
NEEDS_COMPILE
SQL> execute DBMS_MVIEW.REFRESH( 'MV_1B', nested => TRUE );
PL/SQL procedure successfully completed.
SQL> select mview_name, staleness from user_mviews where mview_name in ('MV_1', 'MV_1A', 'MV_1B') order by mview_name;
MVIEW_NAME
--------------------------------------------------------------------------------
STALENESS
-------------------
MV_1
FRESH
MV_1A
NEEDS_COMPILE
MV_1B
FRESH
SQL> execute DBMS_MVIEW.REFRESH( 'MV_1A', nested => TRUE );
PL/SQL procedure successfully completed.
SQL> select mview_name, staleness from user_mviews where mview_name in ('MV_1', 'MV_1A', 'MV_1B') order by mview_name;
MVIEW_NAME
--------------------------------------------------------------------------------
STALENESS
-------------------
MV_1
FRESH
MV_1A
FRESH
MV_1B
NEEDS_COMPILE
Is there any way we can hint to Oracle that he should not refresh dependent materialized views if they are already fresh?
Thanks for providing a working LiveSQL test case! I feel like you're approaching this in the wrong way though...
MV_1A & B are dependent on just MV_1. Thus you only need to refresh these after refreshing MV_1. So you should refresh MV_1 using DBMS_MVIEW.REFRESH_DEPENDENT and nested => true.
Do this and all the MVs will stay fresh:
create table TABLE_A (
common_id NUMBER(6),
value_a NUMBER(12)
);
create table TABLE_B (
common_id NUMBER(6),
value_b NUMBER(12)
);
create materialized view MV_1 as
(
select common_id, value_a, value_b
from table_a
join table_b using (common_id)
);
create materialized view MV_1A as
(
select common_id, value_a + 1, value_b - 1
from mv_1
);
create materialized view MV_1B as
(
select common_id, value_a + 2, value_b - 2
from mv_1
);
insert into table_a values (1, 100);
insert into table_b values (1, 100);
commit;
select count(*) from mv_1;
COUNT(*)
0
select count(*) from mv_1a;
COUNT(*)
0
select count(*) from mv_1b;
COUNT(*)
0
declare
errs integer;
begin
DBMS_MVIEW.REFRESH_DEPENDENT ( errs, 'MV_1', nested => TRUE );
end;
/
select mview_name, staleness
from user_mviews
where mview_name in ('MV_1', 'MV_1A', 'MV_1B')
order by mview_name;
MVIEW_NAME STALENESS
MV_1 FRESH
MV_1A FRESH
MV_1B FRESH
select count(*) from mv_1;
COUNT(*)
1
select count(*) from mv_1a;
COUNT(*)
1
select count(*) from mv_1b;
COUNT(*)
1