Must Set CONSIDER FRESH every time Primary Table is Updated When QUERY_REWRITE_INTEGRITY = TRUSTED
(Doc ID 2375688.1)
Last updated on JULY 22, 2021
Applies to:Oracle Database - Enterprise Edition - Version 188.8.131.52 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.
There is a need to set materialized view to be used for query rewrite when in stale state. This requirement is for only one Materialized View and should NOT affect other Materialized Views on the system.
ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY = TRUSTED;
ALTER MATERIALIZED VIEW <<<mview name>>> CONSIDER FRESH;
After making these changes, initially it seems to work. However, after changing the underlying table on which the MV is built on, then query rewrite does not work.
These are the steps used to test the behavior:
1. TRUSTED mode ON system wide, CONSIDER REFRESH ON.
2. Change data in underlying table. Query rewrite does not work.
3. Reapply CONSIDER REFRESH on. Query rewrite works.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document