Last updated on JANUARY 18, 2017
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.
The defining query of the Materialized View references SYNONYMs, not just the source objects such as other tables and views.
A call to DBMS_MVIEW.EXPLAIN_REWRITE with the same query (text) that is used in the SELECT clause of the Materialized View, the query is returned as "not rewritten". It only shows as "rewritten" if the DBMS_MVIEW.EXPLAIN_REWRITE command is run as the same user that owns the Materialized View.
A Materialized View defined as "ENABLE QUERY REWRITE" is utilized for query rewrite only for the user that owns the Materialized View.
Other users do not benefit from the query rewrite.
The relevant parameters are:
The system privilege "GLOBAL QUERY REWRITE" is granted to the user executing the query. This should allow query rewrite to use Materialized Views owned by other schemas / users, but the query still does not get rewritten.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms