It appears USING TRUSTED CONSTRAINTS causes a materialized view to have staleness=UNKNOWN when it would otherwise be FRESH.
Is it possible to have a materialized view with staleness=FRESH when USING TRUSTED CONSTRAINTS?
If not, would the optimizer be less likely to consider a materialized view with staleness=UNKNOWN for query rewrite if query_rewrite_integrity=TRUSTED and query_rewrite_enabled=TRUE? How about if query_rewrite_enabled=ENFORCED?
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 -- refresh using trusted constraints
5 as select id
6 from T
7 /
Materialized view created.
SQL>create materialized view T_trusted_MV
2 ( id
3 )
4 refresh using trusted constraints
5 as select id
6 from T
7 /
Materialized view created.
SQL>
SQL>select mview_name, staleness, compile_state, last_refresh_type from user_mviews where mview_name like 'T\_%MV' escape '\' order by 1
2 /
MVIEW_NAME STALENESS COMPILE_STATE LAST_REF
-------------------- ------------------- ------------------- --------
T_MV FRESH VALID COMPLETE
T_TRUSTED_MV UNKNOWN VALID COMPLETE
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 dbms_snapshot.refresh( list => user || '.T_TRUSTED_MV'
9 , purge_option => 2
10 , atomic_refresh => false
11 , out_of_place => true
12 )
13 ;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL>select mview_name, staleness, compile_state, last_refresh_type from user_mviews where mview_name like 'T\_%MV' escape '\' order by 1
2 /
MVIEW_NAME STALENESS COMPILE_STATE LAST_REF
-------------------- ------------------- ------------------- --------
T_MV FRESH VALID COMPLETE
T_TRUSTED_MV UNKNOWN VALID COMPLETE
SQL>select * from T_MV order by id
2 /
ID
-----------
1
SQL>select * from T_trusted_MV order by id
2 /
ID
-----------
1
SQL>drop materialized view T_MV
2 /
Materialized view dropped.
SQL>drop materialized view T_trusted_MV
2 /
Materialized view dropped.
SQL>drop table T
2 /
Table dropped.
SQL>