In SQL queries, cost-based transform hints specify when 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 coalesce subqueries in the query. Its reverse operation is NO_COALESCE_SQ. |
NO_COALESCE_SQ |
Disables subquery coalescing. Its reverse operation is COALESCE_SQ. |
MERGE |
Allows merging views. Its reverse operation is NO_MERGE. |
NO_MERGE |
Disables merging external queries with any inline view queries into a single query. Its reverse operation is MERGE. |
NO_EXPAND |
Disables query expansion for WHERE clauses with OR conditions. Its reverse operation is USE_CONCAT. |
USE_CONCAT |
Enables query expansion for WHERE clauses with 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 with 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 eliminates unnecessary joins and uses window functions to optimize specific aggregation and self-join queries. 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 are to be coalesced. The reverse of this hint is the NO_COALESCE_SQ hint.
Syntax
/*+ COALESCE_SQ [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
qb_name_list: Optional. Specifies a list of one or more query block names. If specified, the hint applies only to the specified query blocks. If not specified, the hint applies to all possible subqueries that can be coalesced.
Examples
The following query example specifies that only the SEL1 and SEL2 query blocks are to be coalesced.
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 in a query are not to be coalesced. The reverse of this hint is the COALESCE_SQ hint.
Syntax
/*+ NO_COALESCE_SQ [ ( [ @ qb_name ] ) ] */
Parameters
qb_name: Optional. Specifies a query block name. If specified, the hint applies only to the specified query block. If not specified, the hint applies to all possible subqueries that can be coalesced.
Examples
The following query example specifies that no subqueries are to be coalesced, maintaining 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 that view merging query rewriting is enabled. This includes both simple and complex view merging. The reverse of this hint is the NO_MERGE hint.
Syntax
/*+ MERGE [ ( [ @ qb_name ] [ > upper_qb_name ] ) ] */
Parameters
upper_qb_name: Optional. Specifies the specific form of view merging query rewriting. The default value is recommended.
Examples
The following query example uses the MERGE hint to enable view merging query rewriting for subqueries. The MERGE hint is used in conjunction 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 query rewriting is disabled. The reverse of this hint is the MERGE hint.
Syntax
/*+ NO_MERGE [ ( [ @ qb_name ] ) ] */
Examples
The following query example uses the NO_MERGE hint to disable view merging query rewriting for subqueries:
SELECT * FROM (SELECT /*+ NO_MERGE */ * FROM t1);
NO_EXPAND Hint
The NO_EXPAND hint specifies that query expansion rewriting for WHERE clauses containing OR conditions is disabled. The reverse of this hint 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 that query expansion rewriting for WHERE clauses containing OR conditions is enabled. The reverse of this hint is the NO_EXPAND hint.
Syntax
/*+ USE_CONCAT [ ( [ @ qb_name ] [ expand_cond_str ] ) ] */
Parameters
expand_cond_str: Optional. Specifies a particularORcondition for which query expansion rewriting is to be performed. If not specified, the hint applies to 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
The PLACE_GROUP_BY hint specifies that GROUP BY clause pushing query rewriting is enabled. This can reduce the amount of data that needs to be grouped and aggregated in certain cases. The reverse of this hint is the 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 a list of tables for whichGROUP BYclause pushing query rewriting is to be performed. If not specified, the hint applies to all connected tables.
Examples
The following query example uses the PLACE_GROUP_BY hint to instruct the optimizer to attempt to process the GROUP BY clause at a more optimal location 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 that GROUP BY clause pushing query rewriting is disabled. The reverse of this hint is the 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 uses the NO_PLACE_GROUP_BY hint to ensure 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 that semi-join queries containing EXISTS or IN are to be converted into inner join queries. The reverse of this hint 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 applies.table: Optional. Specifies the right table of the semi-join for which query rewriting is to be performed. If not specified, the hint applies to all semi-joins.
Examples
The following query example uses the SEMI_TO_INNER(t2) hint to instruct the optimizer to attempt 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
In the following example, the NO_SEMI_TO_INNER hint ensures 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 rewriting feature, which eliminates unnecessary joins in specific scenarios and optimizes the query using window functions. This hint allows 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 the tables to be included in theWIN MAGICquery rewriting operation. If this parameter is omitted, the hint applies to all tables.
Examples
In the following example, WIN_MAGIC(t1 v) instructs the optimizer to apply window functions to optimize the processing of the t1 table and the 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 rewriting feature. The reverse operation is the WIN_MAGIC hint.
Syntax
/*+ NO_WIN_MAGIC [ ( [ @ qb_name ] ) ] */
Examples
In the following example, the NO_WIN_MAGIC hint ensures that the query remains unchanged and is not 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;