Must Set CONSIDER FRESH every time Master Table is Updated When QUERY_REWRITE_INTEGRITY = TRUSTED
Last updated on APRIL 04, 2018
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms