Why does DML on a materialized view's master table cause the materialized view staleness to change from FRESH to NEEDS_COMPILE? I would have thought it would have changed to STALE. My understanding about NEEDS_COMPILE is that it is supposed to reflect structural changes (DDL), so I must have some gap in my understanding because I am getting NEEDS_COMPILE when only performing DML (not DDL).
SQL>column mview_name format a20
SQL>create table T ( id NUMBER
2 )
3 /
Table created.
SQL>insert into T ( id ) values ( 1 )
2 /
1 row created.
SQL>create materialized view T_MV
2 ( id
3 )
4 as select id
5 from T
6 /
Materialized view created.
SQL>select mview_name, staleness, compile_state, last_refresh_type from user_mviews where mview_name = 'T_MV'
2 /
MVIEW_NAME STALENESS COMPILE_STATE LAST_REF
-------------------- ------------------- ------------------- --------
T_MV FRESH VALID COMPLETE
SQL>select * from T_MV order by id
2 /
ID
-----------
1
SQL>insert into T ( id ) values ( 2 )
2 /
1 row created.
SQL>commit
2 /
Commit complete.
SQL>select mview_name, staleness, compile_state, last_refresh_type from user_mviews where mview_name = 'T_MV'
2 /
MVIEW_NAME STALENESS COMPILE_STATE LAST_REF
-------------------- ------------------- ------------------- --------
T_MV NEEDS_COMPILE NEEDS_COMPILE COMPLETE
SQL>select * from T_MV order by id
2 /
ID
-----------
1
SQL>begin
2 dbms_snapshot.refresh( list => user || '.T_MV'
3 , purge_option => 2
4 , atomic_refresh => false
5 , out_of_place => true
6 )
7 ;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>select mview_name, staleness, compile_state, last_refresh_type from user_mviews where mview_name = 'T_MV'
2 /
MVIEW_NAME STALENESS COMPILE_STATE LAST_REF
-------------------- ------------------- ------------------- --------
T_MV FRESH VALID COMPLETE
SQL>select * from T_MV order by id
2 /
ID
-----------
1
2
SQL>insert into T ( id ) values ( 3 )
2 /
1 row created.
SQL>commit
2 /
Commit complete.
SQL>select mview_name, staleness, compile_state, last_refresh_type from user_mviews where mview_name = 'T_MV'
2 /
MVIEW_NAME STALENESS COMPILE_STATE LAST_REF
-------------------- ------------------- ------------------- --------
T_MV NEEDS_COMPILE NEEDS_COMPILE COMPLETE
SQL>select * from T_MV order by id
2 /
ID
-----------
1
2
SQL>alter materialized view T_MV compile
2 /
Materialized view altered.
SQL>select mview_name, staleness, compile_state, last_refresh_type from user_mviews where mview_name = 'T_MV'
2 /
MVIEW_NAME STALENESS COMPILE_STATE LAST_REF
-------------------- ------------------- ------------------- --------
T_MV STALE VALID COMPLETE
SQL>select * from T_MV order by id
2 /
ID
-----------
1
2
SQL>begin
2 dbms_snapshot.refresh( list => user || '.T_MV'
3 , purge_option => 2
4 , atomic_refresh => false
5 , out_of_place => true
6 )
7 ;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>select mview_name, staleness, compile_state, last_refresh_type from user_mviews where mview_name = 'T_MV'
2 /
MVIEW_NAME STALENESS COMPILE_STATE LAST_REF
-------------------- ------------------- ------------------- --------
T_MV FRESH VALID COMPLETE
SQL>select * from T_MV order by id
2 /
ID
-----------
1
2
3
SQL>drop materialized view T_MV
2 /
Materialized view dropped.
SQL>drop table T
2 /
Table dropped.
SQL>column mview_name clear
SQL>
MOS Note After DML on the Master Table(s) of Local Materialized View, USER_MVIEWS.COMPILE_STATE becomes 'NEEDS_COMPILE' and USER_OBJECTS.STATUS becomes 'INVALID' (Doc ID 264036.1) has a demo similar to this and says:
This is expected behavior. Dependencies related to MVs are automatically maintained to ensure correct operation. When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.