In SQL queries, hints related to cost estimation instruct the optimizer to apply or avoid specific query transformations in certain situations, such as query restructuring, view merging, or view retention.
| Hint | Description |
|---|---|
COALESCE_SQ |
Specifies to merge subqueries in a query. Its reverse operation is NO_COALESCE_SQ. |
NO_COALESCE_SQ |
Specifies not to merge subqueries in a query. Its reverse operation is COALESCE_SQ. |
MERGE |
Specifies to merge views. Its reverse operation is NO_MERGE. |
NO_MERGE |
Specifies not to merge external queries or inline view queries into a single query. Its reverse operation is MERGE. |
NO_EXPAND |
Specifies not to rewrite a WHERE clause that contains an OR condition by using query expansion. Its reverse operation is USE_CONCAT. |
USE_CONCAT |
Specifies to rewrite a WHERE clause that contains an OR condition by using query expansion. Its reverse operation is NO_EXPAND. |
PLACE_GROUP_BY |
Specifies to push down the GROUP BY operator. Its reverse operation is NO_PLACE_GROUP_BY. |
NO_PLACE_GROUP_BY |
Specifies not to push down the GROUP BY operator. Its reverse operation is PLACE_GROUP_BY. |
SEMI_TO_INNER |
Optimizes semi join queries that contain EXISTS or IN operators by converting them into inner join queries. Its reverse operation is NO_SEMI_TO_INNER. |
NO_SEMI_TO_INNER |
Instructs the optimizer not to convert semi join queries into inner join queries. Its reverse operation is SEMI_TO_INNER. |
WIN_MAGIC |
Enables WIN MAGIC query rewriting for aggregate and self-join queries that meet specific conditions. 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
The COALESCE_SQ hint specifies that subqueries in a query be merged and rewritten. The NO_COALESCE_SQ hint is the reverse operation of this hint.
Syntax
/*+ COALESCE_SQ [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
qb_name_list: an optional parameter that is a list of names of one or more query blocks. You can specify this parameter to indicate that subqueries in specific query blocks be merged. If this parameter is not specified, the hint specifies that all subqueries that can be merged be merged.
Examples
In the following example, COALESCE_SQ(("SEL1", "SEL2")) specifies that only subqueries in the SEL1 and SEL2 query blocks 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 specifies that subqueries not be merged and rewritten. The COALESCE_SQ hint is the reverse operation of this hint.
Syntax
/*+ NO_COALESCE_SQ [ ( [ @ qb_name ] ) ] */
Parameters
qb_name: an optional parameter that is the name of a specific query block. You can specify this parameter to indicate that subqueries not be merged in the specified query block. If this parameter is not specified, the hint specifies that subqueries that can be merged not be merged.
Examples
In the following example, the NO_COALESCE_SQ hint is used to prevent subqueries from being merged and to keep 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 merging, which involves simple view merging and complex view merging. The NO_MERGE hint is the reverse operation of this hint.
Syntax
/*+ MERGE [ ( [ @ qb_name ] [ > upper_qb_name ] ) ] */
Parameters
upper_qb_name: an optional parameter that is used to specify the form of view merging. We recommend that you use the default value.
Examples
In the following example, the MERGE hint is used to enable view merging for subqueries, and the MERGE hint is used together with the NO_REWRITE hint to force the optimizer to merge views while disabling other types of query rewriting:
SELECT * FROM (SELECT /*+ MERGE NO_REWRITE */ * FROM t1);
NO_MERGE hint
The NO_MERGE hint specifies that view merging not be performed. The MERGE hint is the reverse operation of this hint.
Syntax
/*+ NO_MERGE [ ( [ @ qb_name ] ) ] */
Examples
In the following example, the NO_MERGE hint is used to disable view merging for subqueries:
SELECT * FROM (SELECT /*+ NO_MERGE */ * FROM t1);
NO_EXPAND hint
The NO_EXPAND hint specifies that query expansion not be performed for a WHERE clause that contains an OR condition. The USE_CONCAT hint is the reverse operation of this hint.
Syntax
/*+ NO_EXPAND [ ( [ @ qb_name ] ) ] */
Examples
-- Use the NO_EXPAND hint to indicate the optimizer not to perform OR expansion on a query that contains an OR condition.
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 transformation for expanding WHERE clauses that contain OR conditions. The NO_EXPAND hint is the reverse operation of the USE_CONCAT hint.
Syntax
/*+ USE_CONCAT [ ( [ @ qb_name ] [ expand_cond_str ] ) ] */
Parameters
expand_cond_str: specifies to apply query transformation for expanding only specificORconditions. If this parameter is not specified, the hint specifies to apply query transformation 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 for 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: Use the
USE_CONCAThint for anORcondition 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 enables query transformation for pushing down the GROUP BY operation in some cases, reducing the amount of data to be grouped and aggregated. The NO_PLACE_GROUP_BY hint is the reverse operation of the PLACE_GROUP_BY hint.
Syntax
/*+ PLACE_GROUP_BY [ ( [ @ qb_name ] [ ( table_list ) ] ) ] */
Parameters
@qb_name: specifies the query block to which the hint is applied. This parameter is optional.table_list: specifies to apply theGROUP BYoperation to specific join tables. This parameter is optional. If this parameter is not specified, the hint specifies to push down theGROUP BYoperation for all join tables.
Examples
In the following example, the PLACE_GROUP_BY hint instructs the optimizer to find a better position for handling the GROUP BY clause 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 transformation for pushing down the GROUP BY operation. The PLACE_GROUP_BY hint is the reverse operation of the NO_PLACE_GROUP_BY hint.
Syntax
/*+ NO_PLACE_GROUP_BY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: specifies the query block to which the hint is applied. This parameter is optional.
Examples
In the following example, the NO_PLACE_GROUP_BY hint ensures that the GROUP BY operation is performed at the original query level without any transformation.
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 joins that contain EXISTS or IN and converts them into inner joins. The reverse operation of this hint is the NO_SEMI_TO_INNER hint.
Syntax
/*+ SEMI_TO_INNER [ ( [ @ qb_name ] [ table ] ) ] */
Parameters
@qb_name: specifies the query block to which the hint is applied. This parameter is optional.table: specifies the right table of the semi join for which a query transformation is to be performed. This parameter is optional. If this parameter is not specified, the hint specifies to apply query transformation for all semi joins. We recommend that you do not specify this parameter when you use this hint.
Examples
In the following example, the SEMI_TO_INNER(t2) hint instructs the optimizer to convert the semi join between t1 and t2 into an inner join for more efficient processing.
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 conversion of semi join queries into inner join queries. The opposite operation of this hint is the SEMI_TO_INNER hint.
Syntax
/*+ NO_SEMI_TO_INNER [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: the query block on which the hint is to be applied. This parameter is optional.
Examples
In the following example, the optimizer does not convert the EXISTS subquery from a semi join into an inner join when it derives the result set, because the NO_SEMI_TO_INNER hint is specified.
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 to eliminate joins in aggregate and self-join queries that meet specific conditions, and optimize them by using window functions. This hint guides the optimizer to remove unnecessary joins and replace them with window functions to improve query execution efficiency. The opposite operation of this hint is the NO_WIN_MAGIC hint.
Syntax
/*+ WIN_MAGIC [ ( [ @ qb_name ] [ table_list ] ) ] */
Parameters
table_list: the list of tables that are to participate inWIN_MAGICquery rewriting. This parameter is optional. If this parameter is not specified, the optimizer attempts to rewrite all tables by using theWIN_MAGIChint.
Examples
In the following example, the WIN_MAGIC(t1 v) hint instructs the optimizer to attempt to apply window functions to optimize 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 opposite operation of this hint is the WIN_MAGIC hint.
Syntax
/*+ NO_WIN_MAGIC [ ( [ @ qb_name ] ) ] */
Examples
In the following example, the NO_WIN_MAGIC hint keeps the query unchanged and prevents the optimizer from rewriting the query by 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;