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 NOVEMBER 07, 2023

Applies to:

Goal

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.

Solution

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