Which statement wold you use to stop the following query from executing if it does not rewrite?

You have a materialized view called emp-mv on the emp table. You want to stop the following query from executing if it does not rewrite:
SELECT deptno, sum(sal) FROM emp GROUP BY deptno;
Which statement wold you use?

You have a materialized view called emp-mv on the emp table. You want to stop the following query from executing if it does not rewrite:

SELECT deptno, sum(sal) FROM emp GROUP BY deptno;

Which statement wold you use?

A.
SELECT /*+ USE_CONCAT */deptno,sum(sal) FROM emp GROUOP BE deptno;

B.
SELECT /*+ NO_EXPAND */deptno,sum(sal) FROM emp GROUOP BE deptno;

C.
SELECT /*+ NO_REWRITE */deptno,sum(sal) FROM emp GROUOP BE deptno;

D.
SELECT /*+ REWRITE_OR_ERROR */deptno,sum(sal) FROM emp GROUP BY deptno;

E.
SELECT /*+ NO_QUERY_TRANSFORMATION */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



Leave a Reply 0

Your email address will not be published. Required fields are marked *