Query Rewrite Fails If Mv Uses Asni Join

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

Solution 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%'ORDERBY 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 isnot possible or PCT isnot 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.

truncatetablemv_capabilities_table;
execdbms_mview.explain_mview(q'[select A.y, B.z from A, B where A.x = B.x]');

CAPABILITY_NAMEPREL_TEXTMSGTXT---------------------------------------------------------------------------------------------------REWRITEYREWRITE_FULL_TEXT_MATCHYREWRITE_PARTIAL_TEXT_MATCHYREWRITE_GENERALYREWRITE_PCTNgeneralrewriteisnotpossibleorPCTisnotpossibleonanPCT_TABLE_REWRITENArelationisnotapartitionedtablePCT_TABLE_REWRITENBrelationisnotapartitionedtable

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
  asselect
    A.y, B.z from A, B where A.x = B.x

EXPLAIN PLAN  SET STATEMENT_ID ='jara1'into   plan_table  FORselect   A.y, B.z from A join B on A.x = B.x where y ='A'and z ='Z'
;
  
SELECT*FROMtable(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')