Transform Hint under the Global Hints category

Transform Hints under the Global Hints category are used to provide hints to the optimizer on how to handle or transform the entire query.

NO_QUERY_TRANSFORMATION Hint

The NO_QUERY_TRANSFORMATION hint disables any query rewriting for the current query. Unlike the NO_REWRITE hint in Query Block Hints, using NO_QUERY_TRANSFORMATION prevents the use of Query Block Hints to enable certain rewritings.

For more information about query rewriting, see Overview of Query Rewriting.

Syntax

/*+ NO_QUERY_TRANSFORMATION */

Example

-- Use the `NO_QUERY_TRANSFORMATION` hint to disable any query rewriting.
SELECT /*+ NO_QUERY_TRANSFORMATION */ *
FROM (SELECT * FROM t1) v WHERE v.c1 = 3;

NO_COST_BASED_QUERY_TRANSFORMATION Hint

The NO_COST_BASED_QUERY_TRANSFORMATION hint disables cost-based query rewriting for the current query. For more information about cost-based query rewriting, see Cost-Based Query Rewriting.

Syntax

/*+ NO_COST_BASED_QUERY_TRANSFORMATION */

Example

-- Use the `NO_COST_BASED_QUERY_TRANSFORMATION` hint to disable cost-based query rewriting.
CREATE TABLE t1(c1 int, c2 int, c3 int, index idx1(c1), index idx2(c2));
SELECT /*+ NO_COST_BASED_QUERY_TRANSFORMATION */ *
FROM t1
WHERE c1 = 1 OR c2 = 1;