Other hints under the Transform Hint type

DECORRELATE Hint

The DECORRELATE hint enables decorrelation rewriting for lateral tables in queries. Decorrelation rewriting converts correlated subqueries that depend on the outer query into independent queries or uncorrelated subqueries.

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

Syntax

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

Parameters

• @qb_name: Optional. Specifies the name of the query block to which decorrelation rewriting is applied.

Examples

The following example uses the DECORRELATE hint to enable decorrelation rewriting for lateral tables in queries.

-- The `NO_MERGE` hint and the `DECORRELATE` hint are used to ensure that the subquery is not merged with the outer query and to explicitly request that the optimizer 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 is the reverse operation of the DECORRELATE hint. It disables decorrelation rewriting for lateral tables in queries.

Syntax

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

Parameters

• @qb_name: Optional. Specifies the name of the query block to which decorrelation rewriting is not applied.

Examples

The following example uses the NO_DECORRELATE hint to disable decorrelation rewriting for lateral tables in queries.

-- The `NO_MERGE` hint and the `NO_DECORRELATE` hint are used to ensure that the optimizer does not merge the subquery with the outer query and does not perform decorrelation rewriting.
SELECT *
FROM t1, LATERAL (SELECT /*+ NO_MERGE NO_DECORRELATE */ *
                  FROM t2
                  WHERE t2.c2 = t1.c1) v
WHERE v.c1 = 1;