Stmt Transform Hint

The query transformation hint (Stmt Transform Hint) allows you to control specific query transformation operations in an SQL query.

NO_REWRITE Hint

The NO_REWRITE hint is used to prohibit query rewriting. After using this hint, the query block (QB) it affects will not participate in any query rewriting operations.

Syntax

/*+ NO_REWRITE [ ( [ @ qb_name ] ) ] */

Parameters

• @qb_name: Optional. Specifies the name of the query block to which the NO_REWRITE attribute applies. If no query block name is specified, the hint will affect 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 rewriting hints to allow specific types of query rewriting. For example, using the MERGE hint in an inner query block can specify view merging rewriting, even if the outer query block uses the NO_REWRITE hint.

-- The outer query uses the NO_REWRITE hint, so no automatic rewriting is performed.
SELECT /*+ NO_REWRITE */
    *
FROM
    (SELECT /*+ MERGE */ * FROM t1) -- The inner query uses the MERGE hint to force view merging rewriting
WHERE
    t1.column1 = 'somevalue';