SUB-OPTIMAL MATERIALIZED VIEW USED DURING QUERY REWRITE

(Doc ID 2361951.1)

Last updated on FEBRUARY 19, 2018

Applies to:

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

Symptoms

Consider, there is a SQL query and there are 3 Identical MVIEWs.
All three MVIEWs are eligible for query rewrite.

In 12c, Oracle chooses the MVIEW based on its OBJECT ID order.

For example, if MV1 Object ID is 100, MV2 Object ID is 101 and MV3 Object ID is 102, then 
Oracle chooses MV1 for query rewrite.

If we recreate the MVIEW in different order, say, MV2 Object ID is 100, MV3 
Object ID is 101 and MV1 Object ID is 102, then Oracle chooses MV2 for query rewrite.

If we again recreate the MVIEW in another order, say, MV3 Object ID is 100, MV1 
Object ID is 101 and MV2 Object ID is 102, then Oracle chooses MV3 for query rewrite.

So, basically, in 12c, Oracle chooses the first available MVIEW (order by OBJECT_ID asc) among the available identical MVIEWs.


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