My Oracle Support Banner

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

Last updated on MARCH 27, 2014

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.MONTH_NO from BIDATA_MAIN.DF_REPOSITORY_SUBSET DF2, BIDATA_MAIN.DD_TIME T2 where DF2.FINANCIAL_PROCCESSING_DATE_ID = T2.DATE_ID and DF2.ROUTING_CODE_ISS IN ('FPC','NCC') group by T2.MONTH_NO;

but this one performs as expected

alter session set query_rewrite_integrity='ENFORCED';
-- execute query, check execution plan
select DDFPC.MONTH_NO from BIDATA_MAIN.DF_REPOSITORY_SUBSET DF, BIDATA_MAIN.DD_TIME DDFPC where DF.FINANCIAL_PROCCESSING_DATE_ID = DDFPC.DATE_ID and DF.ROUTING_CODE_ISS IN ('FPC','NCC') group by DDFPC.MONTH_NO
--> Correct behavior: query is rewritten to DA_REPOSITORY_MAE_ISS_TRX_MM

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 DA_REPOSITORY_MAE_ISS_TRX_MM has an anchor table DF_REPOSITORY_SUBSET not found in query
--QSM-01336: the materialized view you specified (DA_REPOSITORY_MAE_ISS_TRX_MM) was not used to rewrite the query
--QSM-01033: query rewritten with materialized view, DA_REPOSITORY_MAE_ISS_TRX_DD
--QSM-01102: materialized view, DA_REPOSITORY_MAE_ISS_TRX_MM, requires join back to table, DD_TIME, on column, MONTH_NO
--QSM-01082: Joining materialized view, DA_REPOSITORY_MAE_ISS_TRX_MM, with table, DF_REPOSITORY_SUBSET, not possible
--QSM-01102: materialized view, DA_REPOSITORY_MAE_ISS_TRX_MM, requires join back to table, DF_REPOSITORY_SUBSET, on column, FINANCIAL_PROCCESSING_DATE_ID
--QSM-01072: materialized view, DA_REPOSITORY_MAE_ISS_TRX_MM, and query have different joins between tables, DF_REPOSITORY_SUBSET and DD_TIME



--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 DA_REPOSITORY_MAE_ISS_TRX_MM has an anchor table DF_REPOSITORY_SUBSET not found in query
--QSM-01033: query rewritten with materialized view, DA_REPOSITORY_MAE_ISS_TRX_MM
--QSM-01102: materialized view, DA_REPOSITORY_MAE_ISS_TRX_MM, requires join back to table, DD_TIME, on column, MONTH_NO
--> although errors are received, the execution plan shows a correct access

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


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.