Other hints under the Transform Hint type

DECORRELATE Hint

The DECORRELATE hint enables query rewriting for lateral tables in queries by decorrelating correlated subqueries, which means converting them into independent or non-correlated subqueries.

The reverse operation of the DECORRELATE hint is the NO_DECORRELATE hint, which disables decorrelation rewriting for lateral tables.

Syntax

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

Parameters

• @qb_name: Optional. Specifies the name of the query block for which to dissociate the rewritten query.

Examples

The query examples below show how to use the DECORRELATE hint to enable rewrite of lateral table queries.

-- `NO_MERGE` Hint with `DECORRELATE` is used to ensure that the subquery is not merged with the outer query, and explicitly request the optimizer to decorrelate the subquery
SELECT *
FROM t1, LATERAL (SELECT /*+ NO_MERGE DECORRELATE */ *
                  FROM t2
                  WHERE t2.c2 = t1.c1) v
WHERE v.c1 = 1;

NO_DECORRELATE Hint

The NO_DECORRELATE hint disables query rewriting for lateral tables, which is the reverse of the DECORRELATE hint.

Syntax

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

Parameters

• @qb_name: Optional. The name of the query block that cannot be reassociated.

Examples

The following query examples disable lateral table de-correlation rewrite by using the NO_DECORRELATE hint.

-- The `NO_MERGE` hint, when used with `NO_DECORRELATE`, instructs the optimizer not to merge the subquery into the outer query and to avoid decorrelating the subquery.
SELECT *
FROM t1, LATERAL (SELECT /*+ NO_MERGE NO_DECORRELATE */ *
                  FROM t2
                  WHERE t2.c2 = t1.c1) v
WHERE v.c1 = 1;