Monday, April 15, 2013

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]

No comments :

Post a Comment