In SQL queries, cost-based hints direct the optimizer to perform or avoid specific query transformations under certain conditions, such as rewriting query structures, merging or not merging views, and so on.
Hint type |
Description |
|---|---|
COALESCE_SQ |
Specifies to merge and rewrite subqueries in the query. Its reverse operation is NO_COALESCE_SQ. |
NO_COALESCE_SQ |
Subquery merge query rewrite is prohibited. Its reverse operation is COALESCE_SQ. |
MERGE |
Permits merging views. Its inverse operation is NO_MERGE. |
NO_MERGE |
External queries and inline view queries cannot be combined into a single query. Its inverse operation is MERGE. |
NO_EXPAND |
Prohibits query expansion and rewrite on the WHERE clause that contains an OR condition. Its reverse operation is USE_CONCAT. |
USE_CONCAT |
Enables query expansion and rewrite on the WHERE clause that contains an OR condition. Its inverse operation is NO_EXPAND. |
PLACE_GROUP_BY |
Enable Group By Pushdown query rewrite. Its reverse operation is NO_PLACE_GROUP_BY. |
NO_PLACE_GROUP_BY |
disabled Group By Pushdown query rewrite. Its reverse operation is PLACE_GROUP_BY. |
SEMI_TO_INNER |
Optimize semi-join queries containing EXISTS or IN by converting them into inner join queries. The reverse operation is NO_SEMI_TO_INNER. |
NO_SEMI_TO_INNER |
Prevents the optimizer from converting a semi-join query into an inner join query. Its reverse operation is SEMI_TO_INNER. |
WIN_MAGIC |
Enable WIN MAGIC Query rewrite: Eliminates joins in aggregate and self-join queries that meet specific criteria, then optimizes them 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 |
disabled WIN MAGIC Query rewrite. Its reverse operation is WIN_MAGIC. |
COALESCE_SQ Hint
The COALESCE_SQ hint instructs the query optimizer to merge and rewrite subqueries within a query. Its counterpart is the NO_COALESCE_SQ hint.
Syntax
/*+ COALESCE_SQ [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
qb_name_list: Optional. The list of names for one or more query blocks, specifying which query blocks to merge subqueries into. If this parameter is omitted, the hint instructs the database to merge all subqueries that can be merged.
Examples
In the query example below, COALESCE_SQ(("SEL1", "SEL2")) specifies that only the two Query Blocks SEL1 and SEL2 are to be merged.
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 prohibits the query optimizer from rewriting a query by merging subqueries. Its counterpart is the COALESCE_SQ hint.
Syntax
/*+ NO_COALESCE_SQ [ ( [ @ qb_name ] ) ] */
Parameters
qb_name: Optional. You can specify to disable subquery merging for a specific query block. If this parameter is omitted, the hint specifies to disable subquery merging for all subqueries that can be merged.
Examples
In the query example below, the NO_COALESCE_SQ hint is used to prevent any subquery coalescence 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 enables view merge query rewrite, which involves both simple view merging and complex view merging. Its reverse operation is the NO_MERGE hint.
Syntax
/*+ MERGE [ ( [ @ qb_name ] [ > upper_qb_name ] ) ] */
Parameter description
upper_qb_name: Specifies the specific form of view merge rewrite. It is recommended to use the default value.
Examples
In the query example below, the MERGE hint is used to enable view merge rewrite for subqueries. The MERGE hint is combined with the NO_REWRITE hint to force the optimizer to merge views and disable other types of query rewrite:
SELECT * FROM (SELECT /*+ MERGE NO_REWRITE */ * FROM t1);
NO_MERGE Hint
The NO_MERGE hint prohibits query rewrite for view merging. Its counterpart is the MERGE hint.
Syntax
/*+ NO_MERGE [ ( [ @ qb_name ] ) ] */
Examples
In the query example below, the NO_MERGE hint is used to disable view merge rewrite for subqueries:
SELECT * FROM (SELECT /*+ NO_MERGE */ * FROM t1);
NO_EXPAND Hint
The NO_EXPAND hint prohibits query expansion and rewriting for WHERE clauses containing OR conditions. Its counterpart is the USE_CONCAT hint.
Syntax
/*+ NO_EXPAND [ ( [ @ qb_name ] ) ] */
Examples
-- Use the NO_EXPAND hint to instruct the optimizer not to perform OR expansion on 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
The USE_CONCAT hint enables query expansion and rewrite for WHERE clauses containing OR conditions. Its counterpart is the NO_EXPAND hint.
Syntax
/*+ USE_CONCAT [ ( [ @ qb_name ] [ expand_cond_str ] ) ] */
Parameter description
expand_cond_str: Optional. Specifies to only expand and rewrite queries for specific OR conditions. If this parameter is omitted, the hint specifies to rewrite queries for allORconditions.
Examples
- Example 1: Use the
USE_CONCAThint for 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: Use the
USE_CONCAThint to rewrite 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: Rewrite the
ORcondition in theEXISTSclause using theUSE_CONCAThint
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 enables pushdown query rewrite for GROUP BY, which can reduce the amount of data that needs to be grouped and aggregated in certain scenarios. Its counterpart is the NO/place_GROUP_BY hint.
Syntax
/*+ PLACE_GROUP_BY [ ( [ @ qb_name ] [ ( table_list ) ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block for which the hint is applied.table_list: Optional. You can specify to push down theGROUP BYoperation on specific connected tables. If this parameter is omitted, the hint attempts to push down theGROUP BYoperation on all connected tables.
Examples
In the query example below, the PLACE_GROUP_BY hint instructs the optimizer to try processing the GROUP BY clause at a better 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 disables query rewrite for pushing down GROUP BY. Its counterpart is the PLACE_GROUP_BY hint.
Syntax
/*+ NO_PLACE_GROUP_BY [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block for which the hint is applied.
Examples
In the query example below, 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 enables semi-join queries containing EXISTS or IN to be converted into inner join queries. Its reverse operation is the NO_SEMI_TO_INNER hint.
Syntax
/*+ SEMI_TO_INNER [ ( [ @ qb_name ] [ table ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint is applied.table: Optional. Can specify the right table of a semi-join to rewrite the query for that specific semi-join. When this parameter is omitted by default, the hint specifies to rewrite queries for all semi-joins. It is recommended not to specify this parameter when using the hint.
Examples
In the query example below, the SEMI_TO_INNER(t2) hint instructs the optimizer to try converting 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 into inner join queries. Its 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 is applied.
Examples
In the query example below, using the NO_SEMI_TO_INNER hint ensures that during result set derivation, the optimizer will not attempt to convert the semi-join in the EXISTS clause into an inner join.
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 is used to enable WIN MAGIC query rewriting, which optimizes aggregate and self-join queries that meet specific scenarios by removing joins and then applying window functions. Using this hint guides the optimizer to remove unnecessary joins and replace them with window functions, thereby improving query execution efficiency. Its 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. When this parameter is omitted by default, the hint specifies to attempt query rewriting for all tables.
Examples
In the query example below, WIN_MAGIC(t1 v) instructs the optimizer to attempt applying window functions for optimization on 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 is used to disable WIN MAGIC query rewrite. Its reverse operation is the WIN_MAGIC hint.
Syntax
/*+ NO_WIN_MAGIC [ ( [ @ qb_name ] ) ] */
Examples
In the query example below, the NO_WIN_MAGIC hint is used to keep the query unchanged and avoid rewriting it 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;