TRUNCATE TABLE rewrite_table;

DECLARE
   querytxt VARCHAR2(1500) := 
   ' SELECT p.prod_name, sum(unit_cost) '||
   ' FROM   costs c, products p ' ||
   ' WHERE  c.prod_id = p.prod_id ' ||
   ' AND    c.time_id >= TO_DATE(''01-JAN-2000'',''DD-MON-YYYY'') ' ||
   ' AND    c.time_id < TO_DATE(''01-JAN-2002'',''DD-MON-YYYY'') ' ||
   ' GROUP BY prod_name';
BEGIN   
   dbms_mview.Explain_Rewrite(querytxt, NULL, 'ID1');
END;
/

Rem show final rewrite decision
SELECT message 
FROM   rewrite_table
ORDER BY sequence desc;
