Rewrite_Table

If your query is not using the Materialized View, use the /*+ rewrite(mvname) */ hint first to make sure that the query can be redirecte dto use the MV. Usually if the MV is correctly written to correspond to the query but it is still not used, is because the optimizer obtains a lower cost by not using the Materialized views. Think of indexing the MV or increasing its parallel execution if possible:

ALTER MATERIALIZED VIEW mv_name PARALLEL n

If using the rewrite hint shows that the cost of using the MV is lower but the optimizer still chooses not to use the MV, then you can use the dbms_mview.explain_rewrite package to obtain more info:


BEGIN

DBMS_MVIEW.EXPLAIN_REWRITE('Query','mv_name','s_id');

END;

Then
SELECT * FROM rewrite_table where statement_id = 's_id'

and hopefully that will tell you the reason.