In SQL queries, cost-based hints specify whether the optimizer should apply or avoid specific query transformations, such as rewriting query structures, merging or not merging views, etc.
| Hint Type | Description |
|---|---|
COALESCE_SQ |
Specifies to merge subqueries in the query. Its reverse operation is NO_COALESCE_SQ. |
NO_COALESCE_SQ |
Prohibits subquery merging query rewriting. Its reverse operation is COALESCE_SQ. |
MERGE |
Allows merging views. Its reverse operation is NO_MERGE. |
NO_MERGE |
Disallows merging the external query and any inline view query into a single query. Its reverse operation is MERGE. |
NO_EXPAND |
Prohibits query expansion rewriting for WHERE clauses containing OR conditions. Its reverse operation is USE_CONCAT. |
USE_CONCAT |
Enables query expansion rewriting for WHERE clauses containing OR conditions. Its reverse operation is NO_EXPAND. |
PLACE_GROUP_BY |
Enables Group By down pressure query rewriting. Its reverse operation is NO_PLACE_GROUP_BY. |
NO_PLACE_GROUP_BY |
Disables Group By down pressure query rewriting. Its reverse operation is PLACE_GROUP_BY. |
SEMI_TO_INNER |
Optimizes semi-join queries containing EXISTS or IN by converting them into inner join queries. Its reverse operation is NO_SEMI_TO_INNER. |
NO_SEMI_TO_INNER |
Prevents the optimizer from converting semi-join queries into 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 guides 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
COALESCE_SQ Hint specifies to merge subqueries in the query. The reverse operation is the NO_COALESCE_SQ Hint.
Syntax
/*+ COALESCE_SQ [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
qb_name_list: Optional. A list of one or more query block names. You can specify this parameter to merge subqueries in specific query blocks. If you do not specify this parameter, the hint merges all possible subqueries.
Examples
The following query example specifies to merge only the SEL1 and SEL2 query blocks by using the COALESCE_SQ(("SEL1", "SEL2")) hint.
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
NO_COALESCE_SQ Hint specifies to disable subquery merging. The reverse operation is the COALESCE_SQ Hint.
Syntax
/*+ NO_COALESCE_SQ [ ( [ @ qb_name ] ) ] */
Parameters
qb_name: Optional. You can specify this parameter to disable subquery merging for specific query blocks. If you do not specify this parameter, the hint disables subquery merging for all possible subqueries.
Examples
The following query example specifies to disable subquery merging by using the NO_COALESCE_SQ hint. This preserves 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
MERGE Hint specifies to enable view merging. The reverse operation is the NO_MERGE Hint.
Syntax
/*+ MERGE [ ( [ @ qb_name ] [ > upper_qb_name ] ) ] */
Parameters
upper_qb_name: Optional. You can specify this parameter to specify the specific form of view merging. We recommend that you use the default value.
Examples
The following query example specifies to enable view merging by using the MERGE hint. 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
NO_MERGE Hint specifies to disable view merging. The reverse operation is the MERGE Hint.
Syntax
/*+ NO_MERGE [ ( [ @ qb_name ] ) ] */
Examples
The following query example specifies to disable view merging by using the NO_MERGE hint.
SELECT * FROM (SELECT /*+ NO_MERGE */ * FROM t1);
NO_EXPAND Hint
NO_EXPAND Hint specifies to disable query expansion for WHERE clauses that contain OR conditions. The 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
USE_CONCAT Hint specifies to enable query expansion for WHERE clauses that contain OR conditions. The reverse operation is the NO_EXPAND Hint.
Syntax
/*+ USE_CONCAT [ ( [ @ qb_name ] [ expand_cond_str ] ) ] */
Parameters
expand_cond_str: Optional. You can specify this parameter to enable query expansion for specificORconditions. If you do not specify this parameter, the hint enables query expansion for allORconditions.
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
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. The reverse operation is the NO_PLACE_GROUP_BY Hint.
Syntax
/*+ PLACE_GROUP_BY [ ( [ @ qb_name ] [ ( table_list ) ] ) ] */
Parameters
@qb_name: Optional. The query block to which the hint applies.table_list: Optional. You can specify this parameter to enableGROUP BYpushdown for specific joined tables. If you do not specify this parameter, the hint enablesGROUP BYpushdown for all joined tables.
Examples
The following query example specifies to enable GROUP BY pushdown by using the PLACE_GROUP_BY hint.
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
NO_PLACE_GROUP_BY Hint specifies to disable GROUP BY pushdown. The reverse operation is the PLACE_GROUP_BY Hint.
Syntax
/*+ NO_PLACE_GROUP_BY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. The query block to which the hint applies.
Examples
The following query example specifies to disable GROUP BY pushdown by using the NO_PLACE_GROUP_BY hint. This 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
SEMI_TO_INNER Hint specifies to enable semi-join queries that contain EXISTS or IN clauses and convert them to inner join queries. The reverse operation is the NO_SEMI_TO_INNER Hint.
Syntax
/*+ SEMI_TO_INNER [ ( [ @ qb_name ] [ table ] ) ] */
Parameters
@qb_name: Optional. The query block to which the hint applies.table: Optional. You can specify this parameter to enable query rewriting for specific semi-joins. If you do not specify this parameter, the hint enables query rewriting for all semi-joins. We recommend that you do not specify this parameter when you use the hint.
Examples
The following query example specifies to enable semi-join queries by using the SEMI_TO_INNER(t2) hint. This instructs the optimizer 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 WIN MAGIC query rewriting, 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 rewriting operation. If this parameter is omitted, the hint applies to all tables.
Examples
The following query example uses WIN_MAGIC(t1 v) to instruct the optimizer to apply window functions to optimize the processing of table t1 and its 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 WIN MAGIC query rewriting. 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;