Stmt Transform Hint

Stmt Transform Hint allows you to control specific query transformation operations in an SQL query.

NO_REWRITE Hint

The NO_REWRITE hint prevents query rewriting. After using this hint, the query block 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 is applied. If you do not specify a query block name, the hint affects the entire SQL statement.

Examples

-- The following query does not perform view merging because the NO_REWRITE hint is specified.
SELECT /*+ NO_REWRITE */ * FROM (SELECT * FROM t1);

Although the NO_REWRITE hint prevents query rewriting, you can combine it with other query rewriting hints to allow specific types of query rewriting. For example, you can use the MERGE hint in an inner query block to 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';