Query Rewrite Fails if MV uses ASNI Join

Question

I cannot get a select statement (very last line in the script below) to query rewrite to use a materialized view. Unfortunately, I don't have a REWRITE_TABLE, so I cannot use dbms_view.explain_rewrite. I am using the execution plan to confirm that it is not being rewritten to use the materialized view. All parameters (paramaters are shown near the end of the below script) and clauses seem to be in place. Even when the REWRITE hint is used, it is still not rewriting. What am I missing? Thank you in advance.

drop table A;

create table A
  (
    x number not null
    , y char(1 byte) not null
  )
;

insert into A (x, y) select level, chr(mod(level-1,10) + ascii('A')) 
  from dual connect by level <= 1000
;

create unique index IU_A_x ON A (x);
create index IX_A_y ON A (y);

alter table A 
  add (
    constraint PK_A primary key ( x )
      rely
      using index IU_A_x
      enable validate
  )
;

--analyze table statistics for A

drop table B;

create table B
  (
    x number not null
    , z char(1 byte) not null
  )
;
  
insert into B (x, z) select mod(level,1000)+1, chr(mod(level-1,26) + ascii('A')) 
  from dual connect by level <= 1000000
;

create index IX_B_x ON B (x);
create index IX_B_z ON B (z);

alter table B 
  add (
    constraint R_B_x foreign key ( x )
      references A (x)
      rely
      enable validate
  )
;

--analyze table statistics for B

drop materialized view MV;

create materialized view MV 
  (
    y
    , z
  )
  build immediate
  refresh force on demand
  with primary key
    using trusted constraints
  enable query rewrite
  as 
  select
    A.y, B.z from A join B on A.x = B.x
;

create index IX_MV_y ON MV (y);
create index IX_MV_z ON MV (z);

--analyze table statistics for MV

set serveroutput on;
show parameters optimizer_mode; --all_rows
show parameters query_rewrite_enabled; -- true
show parameters query_rewrite_integrity; --enforced
select /*+ rewrite */ A.y, B.z from A join B on A.x = B.x where y = 'A' and z = 'Z';

Answer 1

I can't realy explain why, but I made an observation that can make you to workaround the problem.

Here is the result of the explain_mview for the query of your MV

exec dbms_mview.explain_mview(q'[select A.y, B.z from A join B on A.x = B.x]');

SELECT capability_name, possible, SUBSTR(related_text,1,8)
AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
WHERE capability_name   like '%REWRITE%'
ORDER BY seq;

CAPABILITY_NAME                P REL_TEXT MSGTXT                                                      
------------------------------ - -------- ------------------------------------------------------------
REWRITE                        Y                                                                      
REWRITE_FULL_TEXT_MATCH        Y                                                                      
REWRITE_PARTIAL_TEXT_MATCH     Y                                                                      
REWRITE_GENERAL                N          the reason why the capability is disabled has escaped analys
REWRITE_PCT                    N          general rewrite is not possible or PCT is not possible on an

The problem is IMO in the REWRITE_GENERAL = 'N'

If you repeat the same explain_mview only using POJO (= plain old join in Oracle) you'll see a different result.

truncate table mv_capabilities_table;
exec dbms_mview.explain_mview(q'[select A.y, B.z from A, B where A.x = B.x]');

CAPABILITY_NAME                P REL_TEXT MSGTXT                                                      
------------------------------ - -------- ------------------------------------------------------------
REWRITE                        Y                                                                      
REWRITE_FULL_TEXT_MATCH        Y                                                                      
REWRITE_PARTIAL_TEXT_MATCH     Y                                                                      
REWRITE_GENERAL                Y                                                                      
REWRITE_PCT                    N          general rewrite is not possible or PCT is not possible on an
PCT_TABLE_REWRITE              N A        relation is not a partitioned table                         
PCT_TABLE_REWRITE              N B        relation is not a partitioned table

Again important `REWRITE_GENERAL = 'Y'.

Note that I'm using 18.4 XE and this is very suspicious and should be clarifies with Oracle Support.

The final good news is, if you defines the MV with the Oracle join, you may use the ASNI join and you will see the rewrite:

Example

create materialized view MV2 
  enable query rewrite
  as 
  select
    A.y, B.z from A, B where A.x = B.x

EXPLAIN PLAN  SET STATEMENT_ID = 'jara1' into   plan_table  FOR
select   A.y, B.z from A join B on A.x = B.x where y = 'A' and z = 'Z'
;
  
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'jara1','ALL'));

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |  3846 | 15384 |   456   (8)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV2  |  3846 | 15384 |   456   (8)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("MV2"."Z"='Z' AND "MV2"."Y"='A')