My Oracle Support Banner

QUERY NOT REWRITE AS EXPECTED WHEN ALIASES ARE USED (Doc ID 1560685.1)

Last updated on FEBRUARY 28, 2019

Applies to:

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

Symptoms

On production database 11.2.0.2 version, RDBMS

ACTUAL BEHAVIOR
---------------
The following query does not rewrite:

alter session set query_rewrite_integrity='ENFORCED';
-- execute query, check execution plan
select T2.COL4 from XYZ.T1 DF2, XYZ.T2 T2 where DF2.COL1 = T2.COL2 and DF2.COL3 IN ('ABC','CDE') group by T2.COL4;

but this one performs as expected

alter session set query_rewrite_integrity='ENFORCED';
-- execute query, check execution plan
select T2.COL4 from XYZ.T1 DF, XYZ.T2 T2 where DF.COL1 = T2.COL2 and DF.COL3 IN ('ABC','CDE') group by T2.COL4
--> Correct behavior: query is rewritten to XXXYYYZZZ

STEPS
-----------------------
--execute rewrite explain: results for the first query
--QSM-01151: query was rewritten
--QSM-01123: self-join algorithm could not resolve multiple instances
--QSM-01284: materialized view XXXYYYZZZ has an anchor table T1 not found in query
--QSM-01336: the materialized view you specified (XXXYYYZZZ) was not used to rewrite the query
--QSM-01033: query rewritten with materialized view, DA_REPOSITORY_MAE_ISS_TRX_DD
--QSM-01102: materialized view, XXXYYYZZZ, requires join back to table, T2, on column, COL4
--QSM-01082: Joining materialized view, XXXYYYZZZ, with table, T1, not possible
--QSM-01102: materialized view, XXXYYYZZZ, requires join back to table, T1, on column, COL1
--QSM-01072: materialized view, XXXYYYZZZ, and query have different joins between tables, T1 and T2



--execute rewrite explain: results for the second query
--QSM-01151: query was rewritten
--QSM-01123: self-join algorithm could not resolve multiple instances
--QSM-01284: materialized view XXXYYYZZZ has an anchor table T1 not found in query
--QSM-01033: query rewritten with materialized view, XXXYYYZZZ
--QSM-01102: materialized view, XXXYYYZZZ, requires join back to table, T2, on column, COL4
--> although errors are received, the execution plan shows a correct access

Changes

 

Cause

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
Symptoms
Changes
Cause
Solution
References


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