In SQL queries, cost-based transform hints specify whether the optimizer should apply or avoid specific query transformations, such as rewriting the query structure, merging or not merging views, etc., based on cost estimation.
Hint type |
Description |
|---|---|
COALESCE_SQ |
Specifies to merge subqueries in the query. Its reverse operation is NO_COALESCE_SQ. |
NO_COALESCE_SQ |
Prevents subquery merging. Its reverse operation is COALESCE_SQ. |
MERGE |
Allows merging views. Its reverse operation is NO_MERGE. |
NO_MERGE |
Prevents merging external queries with any inline view queries into a single query. Its reverse operation is MERGE. |
NO_EXPAND |
Prevents query expansion for WHERE clauses containing OR conditions. Its reverse operation is USE_CONCAT. |
USE_CONCAT |
Enables query expansion for WHERE clauses containing OR conditions. Its reverse operation is NO_EXPAND. |
PLACE_GROUP_BY |
Enables Group By pushdown query rewriting. Its reverse operation is NO_PLACE_GROUP_BY. |
NO_PLACE_GROUP_BY |
Disables Group By pushdown query rewriting. Its reverse operation is PLACE_GROUP_BY. |
SEMI_TO_INNER |
Optimizes semi-join queries containing EXISTS or IN by converting them to inner join queries. Its reverse operation is NO_SEMI_TO_INNER. |
NO_SEMI_TO_INNER |
Prevents the optimizer from converting semi-join queries to inner join queries. Its reverse operation is SEMI_TO_INNER. |
WIN_MAGIC |
Enables WIN MAGIC query rewriting, which optimizes specific aggregation and self-join queries by eliminating joins and using window functions. This hint instructs the optimizer to remove unnecessary joins and replace them with window functions. Its reverse operation is NO_WIN_MAGIC. |
NO_WIN_MAGIC |
Disables WIN MAGIC query rewriting. Its reverse operation is WIN_MAGIC. |
COALESCE_SQ Hint
The COALESCE_SQ hint specifies to merge subqueries in a query. Its reverse operation is the NO_COALESCE_SQ hint.
Syntax
/*+ COALESCE_SQ [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
qb_name_list: Optional. The name list of one or more query blocks. You can specify to merge subqueries in specific query blocks. If this parameter is not specified, all subqueries that can be merged are merged.
Examples
In the following example, COALESCE_SQ(("SEL1", "SEL2")) specifies to merge subqueries in only the SEL1 and SEL2 query blocks.
UPDATE /*+ COALESCE_SQ(("SEL1", "SEL2")) */ t1
SET c1 = (SELECT /*+ QB_NAME(SEL1) */ c1 FROM t2 A),
c2 = (SELECT /*+ QB_NAME(SEL2) */ c2 FROM t2 B),
c3 = (SELECT /*+ QB_NAME(SEL3) */ c3 FROM t2 C),
c4 = (SELECT /*+ QB_NAME(SEL4) */ c4 FROM t2 D);
NO_COALESCE_SQ Hint
The NO_COALESCE_SQ hint specifies to disable subquery merging. Its reverse operation is the COALESCE_SQ hint.
Syntax
/*+ NO_COALESCE_SQ [ ( [ @ qb_name ] ) ] */
Parameters
qb_name: Optional. The name of a query block. You can specify to disable subquery merging in a specific query block. If this parameter is not specified, subquery merging is disabled for all subqueries that can be merged.
Examples
In the following example, the NO_COALESCE_SQ hint is used to disable subquery merging and maintain the original query structure:
UPDATE /*+ NO_COALESCE_SQ */ t1
SET c1 = (SELECT /*+ QB_NAME(SEL1) */ c1 FROM t2 A),
c2 = (SELECT /*+ QB_NAME(SEL2) */ c2 FROM t2 B),
c3 = (SELECT /*+ QB_NAME(SEL3) */ c3 FROM t2 C),
c4 = (SELECT /*+ QB_NAME(SEL4) */ c4 FROM t2 D);
MERGE Hint
The MERGE hint specifies to enable view merging. Its reverse operation is the NO_MERGE hint.
Syntax
/*+ MERGE [ ( [ @ qb_name ] [ > upper_qb_name ] ) ] */
Parameters
upper_qb_name: Optional. The name of a query block. You can specify to enable view merging in a specific query block. If this parameter is not specified, view merging is enabled for all query blocks.
Examples
In the following example, the MERGE hint is used to enable view merging. The MERGE hint and the NO_REWRITE hint are used together to force the optimizer to merge views and disable other types of query rewriting:
SELECT * FROM (SELECT /*+ MERGE NO_REWRITE */ * FROM t1);
NO_MERGE Hint
The NO_MERGE hint specifies to disable view merging. Its reverse operation is the MERGE hint.
Syntax
/*+ NO_MERGE [ ( [ @ qb_name ] ) ] */
Examples
In the following example, the NO_MERGE hint is used to disable view merging:
SELECT * FROM (SELECT /*+ NO_MERGE */ * FROM t1);
NO_EXPAND Hint
The NO_EXPAND hint specifies to disable query expansion for WHERE clauses that contain OR conditions. Its reverse operation is the USE_CONCAT hint.
Syntax
/*+ NO_EXPAND [ ( [ @ qb_name ] ) ] */
Examples
-- Use the NO_EXPAND hint to instruct the optimizer not to expand queries with OR conditions.
SELECT /*+ NO_EXPAND */ *
FROM employees e, departments d
WHERE e.manager_id = 108
OR d.department_id = 110;
USE_CONCAT Hint
The USE_CONCAT hint specifies to enable query expansion for WHERE clauses that contain OR conditions. Its reverse operation is the NO_EXPAND hint.
Syntax
/*+ USE_CONCAT [ ( [ @ qb_name ] [ expand_cond_str ] ) ] */
Parameters
expand_cond_str: Optional. The specificORcondition to expand. If this parameter is not specified, allORconditions are expanded.
Examples
- Example 1: Apply the
USE_CONCAThint to the firstORcondition.
SELECT /*+ USE_CONCAT('b.c1 = c.c1 OR b.c2 = c.c2') */ a.c1, b.c1, c.c1
FROM t1 a
LEFT JOIN (t1 b LEFT JOIN t1 c ON b.c3 = 4 AND (b.c1 = c.c1 OR b.c2 = c.c2))
ON a.c1 = b.c1
WHERE (a.c2 = 3 OR a.c3 = 4)
AND EXISTS (SELECT * FROM t1 d WHERE a.c1 = d.c1 OR a.c2 = d.c2);
- Example 2: Apply the
USE_CONCAThint to the secondORcondition.
SELECT /*+ USE_CONCAT('a.c2 = 3 OR a.c3 = 4') */ a.c1, b.c1, c.c1
FROM t1 a
LEFT JOIN (t1 b LEFT JOIN t1 c ON b.c3 = 4 AND (b.c1 = c.c1 OR b.c2 = c.c2))
ON a.c1 = b.c1
WHERE (a.c2 = 3 OR a.c3 = 4)
AND EXISTS (SELECT * FROM t1 d WHERE a.c1 = d.c1 OR a.c2 = d.c2);
- Example 3: Apply the
USE_CONCAThint to theORcondition in theEXISTSclause.
SELECT /*+ USE_CONCAT('a.c1 = VIEW1.d.c1 OR a.c2 = VIEW1.d.c2') */ a.c1, b.c1, c.c1
FROM t1 a
LEFT JOIN (t1 b LEFT JOIN t1 c ON b.c3 = 4 AND (b.c1 = c.c1 OR b.c2 = c.c2))
ON a.c1 = b.c1
WHERE (a.c2 = 3 OR a.c3 = 4)
AND EXISTS (SELECT * FROM t1 d WHERE a.c1 = d.c1 OR a.c2 = d.c2);
PLACE_GROUP_BY Hint
The PLACE_GROUP_BY hint specifies to enable GROUP BY pushdown. This can reduce the amount of data that needs to be grouped and aggregated in some cases. Its reverse operation is the NO_PLACE_GROUP_BY hint.
Syntax
/*+ PLACE_GROUP_BY [ ( [ @ qb_name ] [ ( table_list ) ] ) ] */
Parameters
@qb_name: Optional. The name of the query block to which the hint applies.table_list: Optional. The list of tables to whichGROUP BYpushdown applies. If this parameter is not specified,GROUP BYpushdown is attempted for all tables.
Examples
In the following example, the PLACE_GROUP_BY hint instructs the optimizer to try to process the GROUP BY clause at a more optimal position in the execution plan.
SELECT /*+ PLACE_GROUP_BY */ SUM(t2.c3)
FROM t1
JOIN t2 ON t1.c2 = t2.c2
GROUP BY t1.c2;
NO_PLACE_GROUP_BY Hint
The NO_PLACE_GROUP_BY hint specifies to disable GROUP BY pushdown. Its reverse operation is the PLACE_GROUP_BY hint.
Syntax
/*+ NO_PLACE_GROUP_BY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. The name of the query block to which the hint applies.
Examples
In the following example, the NO_PLACE_GROUP_BY hint ensures that the GROUP BY operation occurs at the original query level without any rewriting.
SELECT /*+ NO_PLACE_GROUP_BY */ SUM(t2.c3)
FROM t1
JOIN t2 ON t1.c2 = t2.c2
GROUP BY t1.c2;
SEMI_TO_INNER Hint
The SEMI_TO_INNER hint specifies to convert semi-joins that contain EXISTS or IN clauses into inner joins. Its reverse operation is the NO_SEMI_TO_INNER hint.
Syntax
/*+ SEMI_TO_INNER [ ( [ @ qb_name ] [ table ] ) ] */
Parameters
@qb_name: Optional. The name of the query block to which the hint applies.table: Optional. The right table of the semi-join. You can specify to convert a specific semi-join into an inner join. If this parameter is not specified, all semi-joins are converted into inner joins. We recommend that you do not specify this parameter when you use the hint.
Examples
In the following example, the SEMI_TO_INNER(t2) hint instructs the optimizer to try to convert the semi-join between t1 and t2 into a more efficient inner join.
SELECT /*+ SEMI_TO_INNER(t2) */ *
FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.c1 = t2.c2);
NO_SEMI_TO_INNER Hint
The NO_SEMI_TO_INNER hint disables the conversion of semi join queries to inner join queries. The reverse operation is the SEMI_TO_INNER hint.
Syntax
/*+ NO_SEMI_TO_INNER [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following query example uses the NO_SEMI_TO_INNER hint to ensure that the optimizer does not attempt to convert the semi join in the EXISTS clause to an inner join when deriving the result set.
SELECT /*+ NO_SEMI_TO_INNER */ *
FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.c1 = t2.c2);
WIN_MAGIC Hint
The WIN_MAGIC hint enables the WIN MAGIC query rewrite, which eliminates joins in specific scenarios and optimizes the queries using window functions. This hint instructs the optimizer to remove unnecessary joins and replace them with window functions, thereby improving query execution efficiency. The reverse operation is the NO_WIN_MAGIC hint.
Syntax
/*+ WIN_MAGIC [ ( [ @ qb_name ] [ table_list ] ) ] */
Parameters
table_list: Optional. Specifies which tables should participate in theWIN_MAGICquery rewrite. If this parameter is omitted, the hint applies to all tables.
Examples
The following query example uses the WIN_MAGIC(t1 v) hint to instruct the optimizer to apply window functions to optimize the processing of table t1 and derived table v.
SELECT /*+ WIN_MAGIC(t1 v) */ *
FROM t1
JOIN (SELECT AVG(c1), c2 FROM t1 GROUP BY c2) v ON t1.c2 = v.c2
ORDER BY t1.pk;
NO_WIN_MAGIC Hint
The NO_WIN_MAGIC hint disables the WIN MAGIC query rewrite. The reverse operation is the WIN_MAGIC hint.
Syntax
/*+ NO_WIN_MAGIC [ ( [ @ qb_name ] ) ] */
Examples
The following query example uses the NO_WIN_MAGIC hint to keep the query unchanged and prevent it from being rewritten using window functions.
SELECT /*+ NO_WIN_MAGIC */ *
FROM t1
JOIN (SELECT AVG(c1), c2 FROM t1 GROUP BY c2) v ON t1.c2 = v.c2
ORDER BY t1.pk;