Skip to Main Content
  • Questions
  • Materialized View USING TRUSTED CONSTRAINTS and staleness=UNKNOWN

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 06, 2025 - 4:36 pm UTC

Last updated: September 08, 2025 - 4:59 pm UTC

Version: 19c EE

Viewed 100+ times

You Asked

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>

and Chris said...

This is as documented:

Any materialized view that can use TRUSTED constraints for refresh is left in a state of trusted freshness (the UNKNOWN state) after refresh.

https://docs.oracle.com/en/database/oracle/oracle-database/23/dwhsg/basic-materialized-views.html#GUID-B9F8853B-5C0B-4C7A-AB03-CE3D29814A82

Rating

  (1 rating)

Comments

Thanks

A reader, September 08, 2025 - 11:29 pm UTC

Thanks for the doc reference.
Correction, for anyone else coming to this thread:
[FROM]
How about if query_rewrite_enabled=ENFORCED?
[TO]
How about if query_rewrite_integrity=ENFORCED?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library