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

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