QSM-01150: Query Did Not Rewrite For A User Other Than The Owner Of the Materialized View (Doc ID 1594725.1)

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.

Symptoms

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:


optimizer_mode ALL_ROWS
query_rewrite_enabled TRUE
query_rewrite_integrity trusted

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. 

Changes

 None.

Cause

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