Optimizer Doesn't Select Materialized View's Even With Query_Rewrite_Enabled=TRUE (Doc ID 2129679.1)

Last updated on MAY 17, 2016

Applies to:

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

Goal

Upgraded database from 11g or earlier version to 12c. Optimizer does not rewrite query to use mview even with Query_rewrite_enabled=TRUE.

For example, this query

select
   sum(T101836.BURDENED_SERV_ENCUMB_EST_COST) as c15
   from  PA_BUDGET_VERSION_DIM T100882 /* CX_PA_BUDGET_VERSION_DIM */,
   (select *
   from
   CX_PA_PLANS_VS_ACTUALS_AFACT) T101836,
   date_dim d14
   where          T100882.BUDGET_VERSION_KEY = T101836.BUDGET_VERSION_KEY and
   T101836.PA_MONTH_END_DATE = D14.date_key and
   T100882.CX_PREFERRED_VERSION_FLAG = 'Y' and
   d14.cx_fisc_year_nbr = 2013

 

This is execution plan in 12c:

0 | SELECT STATEMENT
1 |  SORT AGGREGATE
2 |   HASH JOIN
3 |    INDEX RANGE SCAN                  | PA_BUDGET_VERSION_MAS_N5
4 |    NESTED LOOPS
5 |     NESTED LOOPS
6 |      INDEX FAST FULL SCAN            | DATE_MAS_U8
7 |      PARTITION RANGE ITERATOR
8 |       BITMAP CONVERSION TO ROWIDS
9 |        BITMAP INDEX SINGLE VALUE     | CX_PA_PLANS_VS_ACT_AFACT_N1_FK
10 |     TABLE ACCESS BY LOCAL INDEX ROWID| CX_PA_PLANS_VS_ACTUALS_AFACT

Add /*+ rewrite */ hint, the plan change to

0 | SELECT STATEMENT
1 |  SORT AGGREGATE
2 |   HASH JOIN
3 |    PART JOIN FILTER CREATE       | :BF0000
4 |     INDEX FAST FULL SCAN         | DATE_MAS_U8
5 |    HASH JOIN
6 |     INDEX RANGE SCAN             | PA_BUDGET_VERSION_MAS_N5
7 |     PARTITION RANGE JOIN-FILTER
8 |      MAT_VIEW REWRITE ACCESS FULL| CX_PA_PVSA_MM_AFACT_M

So, the query is rewritable, but Oracle chooses not to.

Solution

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