You have a materialized view called emp_mv on the emp table. You want to stop following query from executing if it does not rewrite:
SELECT deptno,sum (sal) FROM emp GROUP BY deptno;
Which statement should you use?
A.
SELECT /*+USE_CONTACT*/deptno,sum(sal) FROM emp GROUP BY deptno;
B.
SELECT /*+NO_EXPAND*/deptno,sum(sal) FROM emp GROUP BY deptno;
C.
SELECT /*+NO_REWRITE*/deptno,sum(sal) FROM emp GROUP BY deptno;
D.
SELECT / *+REWRITE_OR_ERROR */deptno,sum(sal) FROM emp GROUP BY deptno;
Explanation:
REWRITE_OR_ERROR Hint
There may be situations where you want to stop the query from executing if it did not rewrite . One such situation can be when you expect the un-rewritten query to take an unacceptably long time to execute. To support this requirement, Oracle Database 10g provides a new hint called REWRITE_OR_ERROR. This is a query block-level hint. For example, if the SELECT statement is not rewritten, the error displayed in the message is thrown. The REWRITE_OR_ERROR hint allows you to run DBMS_MVIEW.EXPLAIN_REWRITE() on the query, resolve the problems that caused rewrite to fail, and run the query again