Transform hints: Stmt transform hint

The statement transformation hint (stmt transform hint) allows you to control specific query transformations in SQL queries.

NO_REWRITE hint

The NO_REWRITE hint prevents query rewriting. After this hint is used, the query block affected by this hint will not participate in any query rewriting operations.

Syntax

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

Parameters

• @qb_name: the name of the query block. Optional. Specifies the name of the query block to which the NO_REWRITE attribute is applied. If the query block name is not specified, the hint affects the entire SQL statement.

Examples

-- The following query will not be rewritten to perform view merging because the NO_REWRITE hint is added.
SELECT /*+ NO_REWRITE */ * FROM (SELECT * FROM t1);

The NO_REWRITE hint prevents query rewriting. However, 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 the inner query block to specify view merging rewriting. Even if the outer query block has the NO_REWRITE hint, the view merging rewriting is forcibly performed.

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