Hint type |
Description |
|---|---|
DECORRELATE |
Enable disjoin query rewrite for lateral tables. Its reverse operation is NO_DECORRELATE. |
NO_DECORRELATE |
Disables the de-correlating query rewrite for lateral tables. Its reverse operation is DECORRELATE. |
DECORRELATE Hint
The DECORRELATE hint is used to enable query rewrite for decoupling lateral tables in a query. Decoupling converts correlated subqueries that depend on external queries into independent or non-correlated subqueries.
The reverse operation of the DECORRELATE hint is the NO_DECORRELATE hint, which is used to disable the de-correlating query rewrite for lateral tables.
Syntax
/*+ DECORRELATE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the name of the query block to which the application will associate the rewritten query.
Examples
In the query example below, the DECORRELATE hint is used to enable disassociation query rewrite for lateral tables in the query.
-- The `NO_MERGE` hint, when used with `DECORRELATE`, ensures that a subquery will not be merged with the outer query and explicitly instructs the optimizer to de-correlate 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
It is the reverse operation of the DECORRELATE hint. The NO_DECORRELATE hint is used to disable the de-correlating query rewrite for lateral tables.
Syntax
/*+ NO_DECORRELATE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the name of the query block for which join elimination query rewrite is prohibited.
Examples
In the query example below, the NO_DECORRELATE hint is used to disable the de-correlate query rewrite for lateral tables.
-- The `NO_MERGE` hint, when used together with `NO-Decorrrelate`, ensures that the optimizer does not merge subqueries into the outer query or perform decorrelating query rewrite.
SELECT *
FROM t1, LATERAL (SELECT /*+ NO_MERGE NO_DECORRELATE */ *
FROM t2
WHERE t2.c2 = t1.c1) v
WHERE v.c1 = 1;