Other TRANSFORM Hint

DECORRELATE Hint

The DECORRELATE hint enables query rewriting for lateral tables by decorrelating correlated subqueries that depend on the outer query. Decorrelating involves transforming these correlated subqueries into independent queries or non-correlated subqueries.

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

Syntax

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

Parameters

• @qb_name : optional, specifies the name of the query block to be deassociated from the query rewrite.

Examples

The following query examples show how to enable decorrelation by using the DECORRELATE hint in a query on a lateral table.

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

NO_DECORRELATE Hint

NO_DECORRELATE is the inverse operation of the DECORRELATE hint and is used to disable decorrelation of lateral tables.

Syntax

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

Parameters

• @qb_name: Optional. The name of the query block to which query rewrite is disabled.

Examples

The following query example shows how to use the NO_DECORRELATE hint to disable lateral table decorrelation rewriting.

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