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 in a query. After using this hint, the affected query block 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 hint applies. If not specified, the hint applies to the entire SQL statement.

Examples

-- The following query does not perform view merging because the NO_REWRITE hint is added.
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, using the MERGE hint in an inner query block can force view merging, 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
WHERE
    t1.column1 = 'somevalue';