Query transform hints (Stmt Transform Hint) allow you to control specific query transformation operations in an SQL query.
Hint type |
Description |
|---|---|
NO_REWRITE |
No form of query rewriting is allowed. |
NO_REWRITE Hint
The NO_REWRITE hint is used to prohibit query rewriting. After this hint is applied, the affected query blocks will not participate in any query rewrite operations.
Syntax
/*+ NO_REWRITE [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the name of the query block to which theNO_REWRITEhint is applied. If not specified, the hint affects the entire SQL statement.
Examples
-- In the following query, the NO_REWRITE hint is added, so the query will not be rewritten for view merging.
SELECT /*+ NO_REWRITE */ * FROM (SELECT * FROM t1);
Although the NO_REWRITE hint is used to prohibit query rewriting, you can combine it with other query rewrite hints to allow specific types of query rewriting. For example, using the MERGE hint in the inner query block can specify view merge rewriting, even if the outer query block uses the NO_REWRITE hint.
-- The outer query uses the NO_REWRITE hint, so automatic query rewrite is not performed.
SELECT /*+ NO_REWRITE */
*
FROM
(SELECT /*+ MERGE */ * FROM t1) -- The inner query uses the MERGE hint to force a view merge rewrite
WHERE
t1.column1 = 'somevalue';