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 rewrite subqueries in the query. The reverse operation is NO_COALESCE_SQ. |
NO_COALESCE_SQ |
Prohibits the optimizer from rewriting subqueries. The reverse operation is COALESCE_SQ. |
MERGE |
Allows the optimizer to merge views. The reverse operation is NO_MERGE. |
NO_MERGE |
Prevents the optimizer from merging the outer query with any inline view into a single query. The reverse operation is MERGE. |
NO_EXPAND |
Prohibits the optimizer from expanding queries with OR conditions in the WHERE clause. The reverse operation is USE_CONCAT. |
USE_CONCAT |
Enables the optimizer to expand queries with OR conditions in the WHERE clause. The reverse operation is NO_EXPAND. |
PLACE_GROUP_BY |
Enables the optimizer to push down Group By queries. The reverse operation is NO_PLACE_GROUP_BY. |
NO_PLACE_GROUP_BY |
Disables the optimizer from pushing down Group By queries. The reverse operation is PLACE_GROUP_BY. |
SEMI_TO_INNER |
Optimizes semi-join queries with EXISTS or IN by converting them to inner join queries. The reverse operation is NO_SEMI_TO_INNER. |
NO_SEMI_TO_INNER |
Prevents the optimizer from converting semi-join queries to inner join queries. The reverse operation is SEMI_TO_INNER. |
WIN_MAGIC |
Enables the optimizer to rewrite queries with specific scenarios, such as eliminating unnecessary joins and using window functions for optimization. The reverse operation is NO_WIN_MAGIC. |
NO_WIN_MAGIC |
Disables the optimizer from rewriting queries with specific scenarios, such as eliminating unnecessary joins and using window functions for optimization. The reverse operation is WIN_MAGIC. |
COALESCE_SQ Hint
COALESCE_SQ Hint specifies to merge subqueries in the query. The reverse operation is NO_COALESCE_SQ Hint.
Syntax
/*+ COALESCE_SQ [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
qb_name_list: Optional. Specifies 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, the hint specifies to merge all possible subqueries.
Examples
The following query example specifies to merge only the subqueries in SEL1 and SEL2.
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 COALESCE_SQ Hint.
Syntax
/*+ NO_COALESCE_SQ [ ( [ @ qb_name ] ) ] */
Parameters
qb_name: Optional. Specifies to disable subquery merging in specific query blocks. If this parameter is not specified, the hint specifies to disable subquery merging in all possible subqueries.
Examples
The following query example specifies to disable subquery merging to 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
MERGE Hint specifies to enable view merging. The reverse operation is NO_MERGE Hint.
Syntax
/*+ MERGE [ ( [ @ qb_name ] [ > upper_qb_name ] ) ] */
Parameters
upper_qb_name: Optional. Specifies the specific form of view merging. We recommend that you use the default value.
Examples
The following query example specifies to enable subquery view merging. The MERGE Hint and 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 MERGE Hint.
Syntax
/*+ NO_MERGE [ ( [ @ qb_name ] ) ] */
Examples
The following query example specifies to disable subquery view merging.
SELECT * FROM (SELECT /*+ NO_MERGE */ * FROM t1);
NO_EXPAND Hint
NO_EXPAND Hint specifies to disable query expansion for WHERE clauses containing OR conditions. The reverse operation is USE_CONCAT Hint.
Syntax
/*+ NO_EXPAND [ ( [ @ qb_name ] ) ] */
Examples
-- Use the NO_EXPAND Hint to instruct the optimizer to not expand queries containing 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 containing OR conditions. The reverse operation is NO_EXPAND Hint.
Syntax
/*+ USE_CONCAT [ ( [ @ qb_name ] [ expand_cond_str ] ) ] */
Parameters
expand_cond_str: Optional. Specifies to expand only specificORconditions. If this parameter is not specified, the hint specifies to expand 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 pushing down. This can reduce the amount of data that needs to be grouped and aggregated in some cases. The reverse operation is NO_PLACE_GROUP_BY Hint.
Syntax
/*+ PLACE_GROUP_BY [ ( [ @ qb_name ] [ ( table_list ) ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.table_list: Optional. Specifies to push downGROUP BYfor specific joined tables. If this parameter is not specified, the hint specifies to attempt to push downGROUP BYfor all joined tables.
Examples
The following query example specifies to attempt 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
NO_PLACE_GROUP_BY Hint specifies to disable GROUP BY pushing down. The reverse operation is PLACE_GROUP_BY Hint.
Syntax
/*+ NO_PLACE_GROUP_BY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following query example specifies to perform the GROUP BY operation 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 containing EXISTS or IN clauses to be converted to inner join queries. The reverse operation is NO_SEMI_TO_INNER Hint.
Syntax
/*+ SEMI_TO_INNER [ ( [ @ qb_name ] [ table ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.table: Optional. Specifies the right table of the semi-join to rewrite specific semi-joins. If this parameter is not specified, the hint specifies to rewrite all semi-joins. We recommend that you do not specify this parameter when you use the hint.
Examples
The following query example specifies to attempt to convert the semi-join between t1 and t2 to an 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 uses window functions for optimization. This hint instructs the optimizer to remove unnecessary joins and replace them with window functions, 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 WIN_MAGIC(t1 v) to instruct the optimizer to apply window functions to optimize processing for 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, avoiding any rewrites 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;