Other hints under the TRANSFORM hint type

DECORRELATE Hint

The DECORRELATE hint enables a query rewrite that decorrelates lateral tables in the query. A correlated subquery is a subquery whose output depends on the outer query. A decorated subquery is a subquery that is independent of the outer query.

The reverse operation of the DECORRELATE hint is the NO_DECORRELATE hint, which disables the de-correlation rewriting of lateral tables.

Syntax

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

Parameters

• @qb_name: Optional. Specifies the name of the query block that the optimizer is to unwrite.

Example

In the following query example, the DECORRELATE Hint is used to enable the rewriting of lateral table queries into a decorrelated form.

-- The `NO_MERGE` hint, when used with the `DECORRELATE` keyword, ensures that the subquery will not be merged with the outer query, and explicitly requests 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

This is the reverse of the DECORRELATE hint. The NO_DECORRELATE hint disables lateral table decorrelation rewriting.

Syntax

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

Parameters

• @qb_name: Optional. Specifies the name of a query block to be excluded from query rewrite.

Examples

The following query examples use the NO_DECORRELATE hint to disable lateral table query decorrelation rewriting.

The `NO_MERGE` hint is used with the `NO_DECORRELATE` hint to ensure that the optimizer does not merge subqueries into the outer query and does not rewrite correlated subqueries.
SELECT *
FROM t1, LATERAL (SELECT /*+ NO_MERGE NO_DECORRELATE */ *
                  FROM t2
                  WHERE t2.c2 = t1.c1) v
WHERE v.c1 = 1;