Investigating Query Rewrite Materialized View issues

Why Oracle does not used a Materialized view altough Query rewrite is enabled?

To answer above question following DBMS_MVIEW.EXPLAIN_REWRITE should be used.

There several options for using DBMS_MVIEW.EXPLAIN_REWRITE , I will mention about the following method to use DBMS_MVIEW.EXPLAIN_REWRITE.

First of all; the table named rewrite_table should be created by using $ORACLE_HOME/rdbms/admin/utlxrw.sql.

Rewrite_table is the table that  DBMS_MVIEW.EXPLAIN_REWRITE writes its output. If the table was already created in previous work, you can truncate it before using DBMS_MVIEW.EXPLAIN_REWRITE again.

Then DBMS_MVIEW.EXPLAIN_REWRITE can be executed. Note that only the query is given as input ( MV name is not given in here). MV name can also be given as input here to find the reason by explicitly specifying the MV name (as second argument), but it is not needed.

Example;

declare

query1 varchar2(256) :=

'select dim1.dk1, dim2.dk2

from fact, dim1, dim2, dim3

where dim1.dk1 = fact.dk1

and dim2.dk2 = fact.dk2

and dim2.dk2 = 1';

begin

dbms_mview.explain_rewrite(query1);

end;

After the procedure is executed, message (the reason) appears in rewrite_table.

In the message column of rewrite_table, reason for not using MV can be found.

select message from rewrite_table order by sequence;

Example : QSM-01094: outer-join filter not found in materialized join view

Reference:  Oracle Support -> How To Use DBMS_MVIEW.EXPLAIN_REWRITE and EXPLAIN_MVIEW To Diagnose Query Rewrite and Fast Refresh Problems [ID 149815.1]