SUB-OPTIMAL MATERIALIZED VIEW USED DURING QUERY REWRITE
Last updated on FEBRUARY 19, 2018
Applies to:Oracle Database - Enterprise Edition - Version 188.8.131.52 and later
Information in this document applies to any platform.
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.
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