Query Rewrite Does Not Occur (Doc ID 1055753.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

Adding an additional 'WHERE' condition or changing the range of the filter in the
'WHERE' clause prevents query rewrite.

<Bug 9253380> and <Bug 8977684>


from rewrite_table, for the failing query, the violations seen are to of the type :

QSM-01150: query did not rewrite
QSM-01102: materialized view, NRCP01_MV01, requires join back to table,
DIM_NRCP_RECIPIENT, on column, CURRENT_RECORD_IND
QSM-01102: materialized view, NRCP01_MV01, requires join back to table,
DIM_RECIPIENT, on column, CURRENT_RECORD_IND
QSM-01102: materialized view, NRCP01_MV01, requires join back to table,
DIM_TIME, on column, CURRENT_RECORD_IND
QSM-01102: materialized view, NRCP01_MV01, requires join back to table,
FCT_RECIPIENT_SERVICES, on column, FACT_TYPE
QSM-01102: materialized view, NRCP01_MV01, requires join back to table,
DIM_LOCATION_HIERARCHY, on column, CURRENT_RECORD_IND
QSM-01102: materialized view, NRCP01_MV01, requires join back to table,
DIM_LOCATION_HIERARCHY, on column, SOURCE_SYSTEM
QSM-01102: materialized view, NRCP01_MV01, requires join back to table,
DIM_LOCATION_HIERARCHY, on column, LOCATION_ID
QSM-01082: Joining materialized view, NRCP01_MV01, with table, DIM_RECIPIENT,
not possible
QSM-01102: materialized view, NRCP01_MV01, requires join back to table,
DIM_NRCP_RECIPIENT, on column, RECIPIENT_SK
QSM-01102: materialized view, NRCP01_MV01, requires join back to table,
DIM_NRCP_RECIPIENT, on column, RECIPIENT_TYPE
QSM-01102: materialized view, NRCP01_MV01, requires join back to table,
DIM_RECIPIENT, on column, RECIPIENT_SK
QSM-01102: materialized view, NRCP01_MV01, requires join back to table,
DIM_RECIPIENT, on column, RECIPIENT_TYPE

Changes

1. An additional 'WHERE' condition in comparison to the mview definition
OR

2. Changing the range of the filter in the 'WHERE' clause in comparison to the mview definition
    E.G.        MV contains the following in the where clause:

WHERE ZDWH_F_TRANSACTIONS.TRX_TRADE_DATE_TIME BETWEEN
TIMESTAMP '2009-01-01 00:00:00.000000000'
AND TIMESTAMP '2009-09-28 00:00:00.000000000'

When a query is executed using this same text the query is rewritten.
Rewrite does not happen when the text is changed to this:

WHERE ZDWH_F_TRANSACTIONS.TRX_TRADE_DATE_TIME BETWEEN
TIMESTAMP '2009-01-11 00:00:00.000000000'
AND TIMESTAMP '2009-09-22 00:00:00.000000000'

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