ORA-30393: A query block in the statement cannot be rewritten
There may be situations where you want to stop the query from executing if it did not rewrite.Oracle Database 10g provides a new hint called REWRITE_OR_ERROR. This is a query block-level hint. When the Query cannot be rewritten, the query will throw error and stop.The error is ORA-30393: A query block in the statement cannot be rewrittenREWRITE_ON_ERROR hint is good for when the failed rewrite may cause unexpected lengthy execution. It stop and report error instead
Typically, when the rewrite failed and query stop. You should use EXPLAIN_REWRITE procedure of DBMS_MVIEW PL/SQL package to troubleshoot why the failed mview rewriteExplain the MVIEWSQL> execute dbms_mview.EXPLAIN_REWRITE -( query => 'SELECT s.prod_id -, sum(s.quantity_sold) -FROM sales s -GROUP BY s.prod_id' -, mv => 'SH.SALES_PROD' -, statement_id => 'EXPLAIN_REWRITE demo' -);Check the Explain resultSQL> SELECT messageFROM rewrite_tableWHERE statement_id = 'EXPLAIN_REWRITE demo';