Transform hints: Other hints

DECORRELATE hint

The DECORRELATE hint enables query rewriting to decorrelate lateral tables in a query. Decorrelating converts correlated subqueries that depend on the external query into independent or uncorrelated queries.

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

Syntax

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

Parameters

• @qb_name: optional. The name of the query block to which the decorrelation rewrite is applied.

Examples

The following example shows how to use the DECORRELATE hint to enable query rewriting to decorrelate lateral tables in a query.

-- The `NO_MERGE` hint and `DECORRELATE` hint are used together 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 is the reverse operation of the DECORRELATE hint. It disables query rewriting to decorrelate lateral tables.

Syntax

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

Parameters

• @qb_name: optional. The name of the query block for which the decorrelation rewrite is disabled.

Examples

The following example shows how to use the NO_DECORRELATE hint to disable query rewriting to decorrelate lateral tables.

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