My Oracle Support Banner

Must Set CONSIDER FRESH every time Master Table is Updated When QUERY_REWRITE_INTEGRITY = TRUSTED (Doc ID 2375688.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

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.

Cause

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
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.