My Oracle Support Banner

How to Use MV_MERGE Hint So Materialized Views with ANSI Joins Will Rewrite and Avoid QSM-01150, QSM-01219 (Doc ID 2042031.1)

Last updated on FEBRUARY 27, 2019

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


This note is applicable for Database 12c Release 12.10.1 and forward.

The implementation of ANSI joins in addition to Oracle’s traditional proprietary syntax can lead to limited materialized view rewrite capabilities under some corner case circumstances, specifically when there is a mixture of using ANSI join for the definition of a materialized view and Oracle join notation for query access. The main reason for this behavior has to do with internal processing and compilation structures which cannot be mapped for easy identification through the customer.

While Oracle is working on providing a permanent solution for this in a future release, this limitation can be currently addressed with a more complex internal processing for both the MV metadata creation and the query rewrite, enabled on a per materialized view base.  This support note aims to help you understand how to address the current limitation.

Scenarios that might hit the current limitation

The lack of query rewrite is expected to being caused by the mixture of ANSI join and Oracle join syntax and can be broadly classified of being in one of the following two categories: 

1. A query uses Oracle’s native join syntax while the materialized view definition is using ANSI join syntax.

2. A materialized view has either a RIGHT/LEFT OUTER or a FULL OUTER join and a query uses equivalent ANSI joins, but doesn’t match with the MV textually.



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
 Scenarios that might hit the current limitation
  Working around the current limitation

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