In SQL queries, hints related to heuristics aim to quickly find a feasible solution, although it may not necessarily be the optimal one. Especially in complex queries, the database optimizer attempts to apply various rules and transformation strategies to find the most effective execution plan.
Hint type |
Description |
|---|---|
AGGR_FIRST_UNNEST |
When a query contains aggregate subqueries (such as functions like AVG, SUM, or MAX), the AGGR_FIRST_UNNEST hint enables aggregate-first subquery optimization for aggregate subqueries, improving query rewrite. Its reverse operation is NO_AGGR_FIRST_UNNEST. |
NO_AGGR_FIRST_UNNEST |
Aggregate-first subquery optimization for aggregate subqueries is prohibited. Its reverse operation is AGGR_FIRST_UNNEST. |
COUNT_TO_EXISTS |
When enabled, the COUNT Subquery converted to EXISTS Subqueries are used for query rewriting. Its reverse operation is NO_COUNT_TO_EXISTS. |
NO_COUNT_TO_EXISTS |
Disable the setting to COUNT Subquery transformed into EXISTS Subqueries are used for query rewriting. Its reverse operation is COUNT_TO_EXISTS. |
ELIMINATE_JOIN |
Identify and remove unnecessary join operations from query execution plans. Its reverse operation is NO_ELIMINATE_JOIN. |
NO_ELIMINATE_JOIN |
Does not eliminate join operations from the query execution plan. Its inverse operation is ELIMINATE_JOIN. |
FAST_MINMAX |
Enables query rewriting for MIN or MAX optimization. Its reverse operation is NO_FAST_MINMAX. |
NO_FAST_MINMAX |
Disables query rewriting for MIN or MAX optimization. Its reverse operation is FAST_MINMAX. |
INLINE |
Indicates to the optimizer to expand the common table expression (CTE) and embed its query content directly into the main query. Its reverse operation is MATERIALIZE. |
MATERIALIZE |
Controls whether to extract a CTE or not to expand a CTE. Its reverse operation is INLINE. |
JOIN_FIRST_UNNEST |
When processing subqueries in aggregate functions, the query is rewritten by attempting to use join operations first. Its reverse operation is NO_JOIN_FIRST_UNNEST. |
NO_JOIN_FIRST_UNNEST |
Applying the join-first rewrite strategy is prohibited in aggregate subqueries. Its reverse operation is JOIN_FIRST_UNNEST. |
LEFT_TO_ANTI |
Enables the optimizer to attempt converting outer joins (OUTER JOIN) into anti joins (ANTI JOIN). Its reverse operation is NO_LEFT_TO_ANTI. |
NO_LEFT_TO_ANTI |
Disables the optimizer from attempting to convert outer joins (OUTER JOIN) into anti joins (ANTI JOIN). Its reverse operation is LEFT_TO_ANTI. |
OUTER_TO_INNER |
Enables the optimizer to rewrite outer joins (OUTER JOIN) into inner joins (INNER JOIN) whenever possible. Its reverse operation is NO_OUTER_TO_INNER. |
NO_OUTER_TO_INNER |
Disables the optimizer from rewriting outer joins (OUTER JOIN) into inner joins (INNER JOIN). Its reverse operation is OUTER_TO_INNER. |
PRED_DEDUCE |
Enables the optimizer to generate new predicates from existing ones and optimize query execution through derivation and pushdown. This kind of rewriting is typically effective for nested queries or join queries. Its reverse operation is NO_PRED_DEDUCE. |
NO_PRED_DEDUCE |
Disables the hint that prompts the optimizer to generate new predicates from existing ones. Its reverse operation is PRED_DEDUCE. |
PROJECT_PRUNE |
Informs the optimizer to prune unused projected columns in internal query blocks. This is particularly useful when the query involves multi-table joins and multi-level nested queries. Its reverse operation is NO_PROJECT_PRUNE. |
NO_PROJECT_PRUNE |
Prohibits the optimizer from pruning unused projected columns within internal query blocks. Its reverse operation is PROJECT_PRUNE. |
PULLUP_EXPR |
Enable expression pull-up rewriting. Its inverse operation is NO_PULLUP_EXPR. |
NO_PULLUP_EXPR |
Disables expression pull-up rewriting. Its reverse operation is PULLUP_EXPR. |
PUSH_LIMIT |
Enable pushdown rewrite for limits. Its reverse operation is NO_PUSH_LIMIT. |
NO_PUSH_LIMIT |
Disables limit pushdown optimization. Its reverse operation is PUSH_LIMIT. |
REPLACE_CONST |
Enables constant propagation optimization. Its reverse operation is NO_REPLACE_CONST. |
NO_REPLACE_CONST |
Disables constant propagation optimization. Its reverse operation is REPLACE_CONST. |
SIMPLIFY_DISTINCT |
Enables a series of DISTINCT-related query rewrite. Its reverse operation is NO_SIMPLIFY_DISTINCT. |
NO_SIMPLIFY_DISTINCT |
Prohibits a series of DISTINCT-related query rewrites. Its reverse operation is SIMPLIFY_DISTINCT. |
SIMPLIFY_EXPR |
Enables expression conversion for query rewriting. Its inverse operation is NO_SIMPLIFY_EXPR. |
NO_SIMPLIFY_EXPR |
Expression conversion-related query rewrite is prohibited. Its reverse operation is SIMPLIFY_EXPR. |
SIMPLIFY_GROUP_BY |
Enables query rewrite for Group By operations. Its reverse operation is NO_SIMPLIFY_GROUP_BY. |
NO_SIMPLIFY_GROUP_BY |
Group-by related query rewrite is prohibited. Its reverse operation is SIMPLIFY_GROUP_BY. |
SIMPLIFY_LIMIT |
Enables query rewrite for LIMIT operations. Its reverse operation is NO_SIMPLIFY_LIMIT. |
NO_SIMPLIFY_LIMIT |
Prohibit the rewrite of limit-related queries. Its reverse operation is SIMPLIFY_LIMIT. |
SIMPLIFY_ORDER_BY |
Enable query rewrite for Order By. Its reverse operation is NO_SIMPLIFY_ORDER_BY. |
NO_SIMPLIFY_ORDER_BY |
Order by-related query rewrite is prohibited. Its reverse operation is SIMPLIFY_ORDER_BY. |
SIMPLIFY_SET |
Enables query rewriting related to set queries. Its reverse operation is NO_SIMPLIFY_SET. |
NO_SIMPLIFY_SET |
Prohibit query rewriting related to set queries. Its inverse operation is SIMPLIFY_SET. |
SIMPLIFY_SUBQUERY |
Enables query rewrite for subqueries. Its reverse operation is NO_SIMPLIFY_SUBQUERY. |
NO_SIMPLIFY_SUBQUERY |
Subquery-based query rewrite is prohibited. Its reverse operation is SIMPLIFY_SUBQUERY. |
SIMPLIFY_WINFUNC |
Enables query rewrite for window functions. Its reverse operation is NO_SIMPLIFY_WINFUNC. |
NO_SIMPLIFY_WINFUNC |
Window function-related query rewrite is prohibited. Its reverse operation is SIMPLIFY_WINFUNC. |
UNNEST |
Enable subquery improvement for rewriting. Its reverse operation is NO_UNNEST. |
NO_UNNEST |
Disables subquery improvement for query rewriting. Its reverse operation is UNNEST. |
AGGR_FIRST_UNNEST Hint
The AGGR_FIRST_UNNEST hint instructs the optimizer to prioritize independent aggregate evaluations over merging subqueries that evaluate aggregate functions into the main query when processing such subqueries. Its counterpart is the NO_AGGR_FIRST_UNNEST hint.
It should be noted that using the AGGR_FIRST_UNNEST or NO_AGGR_FIRST_UNNEST hint directly is not recommended, as they directly affect the query rewrite behavior for subquery elevation. Typically, if you need more fundamental control over the elevation behavior of subqueries, it is better to use the UNNEST or NO_UNNEST hint.
Syntax
/*+ AGGR_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the particular query block that should follow the hint.
Examples
In the query example below, the AGGR_FIRST_UNNEST hint is used to enable aggregation for aggregate subqueries, prioritizing subquery elevation in query rewriting.
-- The hint prompts the optimizer to prioritize optimization of the AVG aggregate function in subqueries during query parsing.
SELECT * FROM t1
WHERE t1.c1 > (SELECT /*+ AGGR_FIRST_UNNEST */ AVG(d1)
FROM t2
WHERE t1.c2 = t2.d2);
NO_AGGR_FIRST_UNNEST Hint
The NO_AGGR_FIRST_UNNEST hint prohibits the optimizer from prioritizing independent aggregate evaluations when processing subqueries that evaluate aggregate functions. Its counterpart is the AGGR_FIRST_UNNEST hint.
It should be noted that using the AGGR_FIRST_UNNEST or NO_AGGR_FIRST_UNNEST hint directly is not recommended, as they directly affect the query rewrite behavior for subquery elevation. Typically, if you need more fundamental control over the elevation behavior of subqueries, it is better to use the UNNEST or NO_UNNEST hint.
Syntax
/*+ NO_AGGR_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the particular query block that should follow the hint.
Examples
In the query example below, the NO_AGGR_FIRST_UNNEST hint is used. This query will maintain its original aggregate subquery form and will not undergo query rewriting that prioritizes aggregation.
SELECT * FROM t1
WHERE t1.c1 > (SELECT /*+ NO_AGGR_FIRST_UNNEST */ AVG(d1)
FROM t2
WHERE t1.c2 = t2.d2);
COUNT_TO_EXISTS Hint
The COUNT_TO_EXISTS hint enables the query rewrite that converts a COUNT subquery into an EXISTS subquery. Its counterpart is the NO_COUNT_TO_EXISTS hint.
Syntax
/*+ COUNT_TO_EXISTS [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
qb_name_list: Specifies to rewrite specific query block subqueries. If this parameter is omitted, the hint specifies to attempt rewriting all subqueries.
Examples
In the query example below, the COUNT_TO_EXISTS hint is used to enable query rewrite for the COUNT subquery.
-- The `COUNT_TO_EXISTS` hint instructs the optimizer to convert an embedded `COUNT` subquery into a more efficient `EXISTS` form.
SELECT /*+ COUNT_TO_EXISTS */ * FROM t1
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.c1 = t1.c2) > 0;
NO_COUNT_TO_EXISTS Hint
The COUNT_TO_EXISTS hint disables query rewriting that converts COUNT subqueries into EXISTS subqueries. Its reverse operation is the COUNT_TO_EXISTS hint.
Syntax
/*+ NO_COUNT_TO_EXISTS [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.qb_name_list: Specifies to disable rewriting for specific query block subqueries. If this parameter is omitted, the hint attempts to disable rewriting for all subqueries.
Examples
In the query example below, the NO_COUNT_TO_EXISTS hint is used to prevent query rewriting for the COUNT subquery.
-- The `NO_COUNT_TO_EXISTS` hint keeps the original COUNT subquery unchanged and prevents conversion.
SELECT /*+ NO_COUNT_TO_EXISTS */ * FROM t1
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.c1 = t1.c2) > 0;
ELIMINATE_JOIN Hint
The ELIMINATE_JOIN hint controls query rewrite for join elimination. Its reverse operation is the NO_ELIMINATE_JOIN hint.
Syntax
/*+ ELIMINATE_JOIN [ ( [ @ qb_name ] [ table_list ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.table_list: Optional. You can specify this parameter to eliminate joins for specific connected tables. If this parameter is omitted, the hint attempts to eliminate joins for all connected tables.
Examples
In the query examples below, the ELIMINATE_JOIN hint is used to enable join elimination query rewrite.
-- Use the `ELIMINATE_JOIN` hint to find and eliminate the self-join in t1
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
SELECT /*+ ELIMINATE_JOIN */ *
FROM t1 a, t1 b
WHERE a.c1 = b.c1;
NO_ELIMINATE_JOIN Hint
The NO_ELIMINATE_JOIN hint disables query rewrite for join elimination and retains all specified joins. Its counterpart is the ELIMINATE_JOIN hint.
Syntax
/*+ NO_ELIMINATE_JOIN [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.table_list: Optional. You can specify to disable connection elimination for specific connected tables. If this parameter is omitted, the hint specifies to attempt disabling connection elimination for all connected tables.
Examples
In the query example below, NO_ELIMINATE_JOIN is used to prohibit query rewrite that eliminates joins.
-- Use the `NO_ELIMINATE_JOIN` hint to ensure join operations are retained.
SELECT /*+ NO_ELIMINATE_JOIN */ *
FROM t1 a, t1 b
WHERE a.c1 = b.c1;
FAST_MINMAX Hint
The FAST_MINMAX hint enables query rewriting for queries using the MIN or MAX aggregate function. It instructs the database query optimizer to rewrite the query to retrieve the minimum and maximum values more efficiently. Its counterpart is the NO_FAST_MINMAX hint.
Syntax
/*+ FAST_MINMAX [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, FAST_MINMAX is used to enable query rewrite for MIN/MAX optimization.
CREATE TABLE t1(c1 int primary key, c2 int, c3 int);
-- Use the `FAST_MINMAX` hint to execute the calculation of MAX(c1) in the fastest way.
SELECT /*+ FAST_MINMAX */ MAX(c1) FROM t1;
NO_FAST_MINMAX Hint
The NO_FAST_MINMAX hint disables query rewrite for queries using the MIN or MAX aggregate function. Its counterpart is the FAST_MINMAX hint.
Syntax
/*+ NO_FAST_MINMAX [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, NO_FAST_MINMAX is used to prohibit query rewrite for MIN/MAX optimization.
CREATE TABLE t1(c1 int primary key, c2 int, c3 int);
-- The `FAST_MINMAX` hint is used to prevent the optimizer from accelerating the MAX(c1) operation.
SELECT /*+ NO_FAST_MINMAX */ MAX(c1) FROM t1;
INLINE Hint
The INLINE hint is used to specify the expansion of a common table expression (CTE), integrating the CTE directly into the main query. Its reverse operation is the MATERIALIZE hint.
Syntax
/*+ INLINE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
For the following query, use the INLINE hint to specify the expansion of the CTE defined by with cte.
-- Use the `INLINE` hint to directly use the content of a subquery defined in a WITH CTE within the main query.
WITH cte AS (
SELECT /*+INLINE*/ t1.*
FROM t1
JOIN t2 ON t1.c1 = t2.c1
)
SELECT * FROM cte A, cte B;
MATERIALIZE Hint
The MATERIALIZE hint controls whether to extract a CTE or not to expand a CTE. Its reverse operation is the INLINE hint.
Syntax
/*+ MATERIALIZE [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.qb_name_list: Optional. Disables CTE extraction for specific Query Blocks. When the parameter is missing, no CTE extraction is performed. It is recommended to use theMATERIALIZEhint only within a CTE defined bywith cte.
Examples
For the following query, use the MATERIALIZE hint to specify that the CTE defined by with cte should not be expanded, or to actively extract the CTE for the query blocks in the query.
-- Example 1: Materialize a single CTE
WITH cte AS (
SELECT /*+MATERIALIZE*/ * FROM t1
)
SELECT * FROM cte;
-- Example 2: Materialize multiple query blocks
SELECT /*+MATERIALIZE(("SEL$2", "SEL$3"))*/ v1.*
FROM (SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1) v1,
(SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1) v2,
(SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1) v3,
(SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1) v4;
JOIN_FIRST_UNNEST Hint
The JOIN_FIRST_UNNEST hint enables query rewrite optimization for aggregate subqueries, prioritizing join operations to optimize subqueries containing aggregate functions. Its reverse operation is the NO_JOIN_FIRST_UNNEST hint.
Note that it is not recommended to directly use the JOIN_FIRST_UNNEST/NO_JOIN_FIRST_UNNEST hints to intervene in subquery optimization. For such optimizations, it is recommended to use the UNNEST/NO_UNNEST hints.
Syntax
/*+ JOIN_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, the JOIN_FIRST_UNNEST hint is used to enable join-based subquery evaluation, prioritizing subquery evaluation for query rewrite.
-- When using the `JOIN_FIRST_UNNEST` hint to handle subqueries containing aggregate functions (such as AVG), the join operation in the execution plan is executed first.
SELECT * FROM t1
WHERE t1.c1 > (
SELECT /*+ JOIN_FIRST_UNNEST */ AVG(d1)
FROM t2
WHERE t1.c2 = t2.d2
);
NO_JOIN_FIRST_UNNEST Hint
The NO_JOIN_FIRST_UNNEST hint is used to prohibit the query optimizer from preferentially joining aggregate subqueries during query rewrite. When this hint is used, the query optimizer will not consider strategies that prioritize joining subqueries. Its reverse operation is the JOIN_FIRST_UNNEST hint.
It is important to note that it is not recommended to directly use the JOIN_FIRST_UNNEST or NO_JOIN_FIRST_UNNEST hint to intervene in subquery join promotion. Instead, it is recommended to use the UNNEST or NO_UNNEST hint for such purposes.
Syntax
/*+ NO_JOIN_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, the NO_JOIN_FIRST_UNNEST hint is used to prevent the query optimizer from preferentially joining aggregate subqueries during query rewrite.
-- The `NO_JOIN_FIRST_UNNEST` hint prevents the query optimizer from considering joining the subquery to the outer query.
SELECT * FROM t1
WHERE t1.c1 > (
SELECT /*+ NO_JOIN_FIRST_UNNEST */ AVG(d1)
FROM t2
WHERE t1.c2 = t2.d2
);
LEFT_TO_ANTI Hint
The LEFT_TO_ANTI hint enables the query optimizer to rewrite OUTER JOIN statements as ANTI JOIN statements. Its reverse operation is the NO_LEFT_TO_ANTI hint.
Syntax
/*+ LEFT_TO_ANTI [ ( [ @ qb_name ] [ table_list ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.table_list: Optional. You can specify the right tables of specificLEFT JOINoperations for query rewrite. If this parameter is omitted, the hint specifies to attempt rewriting for allOUTER JOINoperations.
Examples
In the query example below, the LEFT_TO_ANTI hint is used to enable the query optimizer to rewrite OUTER JOIN statements as ANTI JOIN statements.
CREATE TABLE t1(c1 int primary key, c2 int, c3 int);
CREATE TABLE t2(c1 int primary key, c2 int, c3 int);
SELECT /*+ LEFT_TO_ANTI(t2) */ * FROM t1 LEFT JOIN t2 ON t1.c2 = t2.c2 WHERE t2.c1 IS NULL;
NO_LEFT_TO_ANTI Hint
The NO_LEFT_TO_ANTI hint disables the query optimizer from rewriting OUTER JOIN statements as ANTI JOIN statements. Its reverse operation is the LEFT_TO_ANTI hint.
Syntax
/*+ NO_LEFT_TO_ANTI [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, NO_LEFT_TO_ANTI is used to prohibit the query rewrite that converts an OUTER JOIN into an ANTI JOIN.
CREATE TABLE t1(c1 int primary key, c2 int, c3 int);
CREATE TABLE t2(c1 int primary key, c2 int, c3 int);
SELECT /*+ NO_LEFT_TO_ANTI */ * FROM t1
LEFT JOIN t2 ON t1.c2 = t2.c2
WHERE t2.c1 IS NULL;
OUTER_TO_INNER Hint
The OUTER_TO_INNER hint controls the query rewrite of outer joins to inner joins, converting LEFT OUTER JOIN or RIGHT OUTER JOIN into a more efficient INNER JOIN. Its reverse operation is the NO_OUTER_TO_INNER hint.
Syntax
/*+ OUTER_TO_INNER [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, OUTER_TO_INNER is used to enable rewrite of outer join queries into inner join queries.
-- The filter condition `b.c1 > 0` applies only to table b. It implicitly excludes the possibility of null values in the right table during the outer join, allowing this outer join operation to be considered an inner join.
SELECT /*+ OUTER_TO_INNER */ *
FROM t1 a
LEFT JOIN t1 b ON a.c2 = b.c2
WHERE b.c1 > 0;
NO_OUTER_TO_INNER Hint
The NO_OUTER_TO_INNER hint disables query rewrite from outer join to inner join, meaning the query optimizer will not convert an outer join into an inner join. Its reverse operation is the OUTER_TO_INNER hint.
Syntax
/*+ NO_OUTER_TO_INNER [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, NO_OUTER_TO_INNER is used to prohibit the query rewrite from an outer join to an inner join.
-- The conditions in the `WHERE` clause may imply an inner join. The `NO_OUTER_TO_INNER` hint ensures that the logic of the outer join is preserved.
SELECT /*+ NO_OUTER_TO_INNER */ *
FROM t1 a
LEFT JOIN t1 b ON a.c2 = b.c2
WHERE b.c1 > 0;
PRED_DEDUCE Hint
The PRED_DEDUCE hint controls predicate deduction query rewrite, using existing predicates to derive new ones or push down predicates. This kind of rewrite is typically effective for nested queries or join queries. Its reverse operation is the NO_PRED_DEDUCE hint.
Syntax
/*+ PRED_DEDUCE [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, PRED_DEDUCE is used to enable the outer Query Block to participate in predicate deduction for query rewriting.
-- Enable Predicate Derivation to Rewrite Outer Queries
select /*+pred_deduce*/ c1
from (
-- Disable subquery merging to ensure the effectiveness of the NO.Merge hint.
select /*+no_merge*/ t1.* from t1
)
where c1 = 3;
NO_PRED_DEDUCE Hint
The NO_PRED_DEDUCE hint disables the optimizer from generating new predicates from existing ones. Its counterpart is the PRED_DEDUCE hint.
Syntax
/*+ NO_PRED_DEDUCE [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, NO_PRED_DEDUCE is used to prevent the outer Query Block from participating in predicate deduction for query rewrite.
-- Disable Predicate Derivation to Rewrite Outer Queries
select /*+no_pred_deduce*/ c1
from (
-- Disable subquery merging to ensure the effectiveness of the NO.Merge hint.
select /*+no_merge*/ t1.* from t1
)
where c1 = 3;
PROJECT_PRUNE Hint
The PROJECT_PRUNE hint optimizes the query execution process by effectively reducing the amount of data processed during queries, especially for large datasets and complex queries. It reduces the output of query blocks by pruning unnecessary columns. This query rewrite technique, known as project pruning, helps improve query efficiency, particularly when processing inner query blocks. Its reverse operation is the NO_Project_PRUNE hint.
Syntax
/*+ PROJECT_PRUNE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies which specific query block should apply thePROJECT_PRUNEhint, providing finer-grained control over query optimization.
Examples
In the query example below, the PROJECT_PRUNE clause is used to invoke the project pruning rewrite mechanism. This enables the optimizer to prune all output columns in the inline view except for column C1.
select c1 from (select /*+no_merge project_prune*/ t1.* from t1);
NO_PROJECT_PRUNE Hint
The NO_Project_PRUNE hint disables query rewrite operations for project pruning. Project pruning automatically prunes unused output columns during query optimization. When it is necessary to retain these columns due to specific performance or functional requirements, the NOoproject_PRUNE hint is particularly important. Its counterpart is the PROJECT_PRUNE hint.
Syntax
/*+ NO_PROJECT_PRUNE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies which query block should avoid application project pruning. When multiple query blocks exist, this provides more precise control.
Examples
In the query example below, NO_Project_PRUNE is used to disable project pruning. This setting ensures that all output columns except for c1 are retained in the result of the inline view, even if they are not needed.
select c1 from (select /*+no_merge no_project_prune*/ t1.* from t1);
PULLUP_EXPR Hint
The PULLUP_EXPR hint enables the optimizer to pull up subquery expressions to the parent query level. Its counterpart is the NO_PULLUP_expr hint.
Syntax
/*+ PULLUP_EXPR [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, PULLUP_expr is used to enable expression pull-up rewrite. After expression pull-up query rewrite is triggered, the SELECT subquery in the inline view is pulled up to the outer query.
-- Assume that t1 is the existing table structure.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Execute a query with the PULLUP_expr hint
SELECT *
FROM (SELECT /*+no_merge pullup_expr*/ t1.*,
(SELECT c2 FROM t1 WHERE c1 = 1)
FROM t1)
WHERE c1 > 1000;
NO_PULLUP_EXPR Hint
The NO_PULLUP_expr hint disables the query optimizer from performing expression pull-up optimization. Its counterpart is the PULLUP_expr hint.
Syntax
/*+ NO_PULLUP_EXPR [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, NO_PULLUP_EXPR is used to prohibit expression pull-up rewriting. After expression pull-up query rewriting is triggered, the select subquery in the inline view is prevented from being pulled up to the outer layer.
-- Assume that t1 is the existing table structure.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Execute a query with the NO_PULLUP_EXPR hint
SELECT *
FROM (SELECT /*+no_merge no_pullup_expr*/ t1.*,
(SELECT c2 FROM t1 WHERE c1 = 1)
FROM t1)
WHERE c1 > 1000;
PUSH_LIMIT Hint
The PUSH_LIMIT hint is used when processing paginated data. It allows the optimizer to push the LIMIT clause (which restricts the number of rows returned) down to the inner parts of a joined query, such as outer joins or Cartesian products, which can significantly improve the efficiency of paginated queries. Its counterpart is the NO PUSH_LIMIT hint.
Syntax
/*+ PUSH_LIMIT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the following query examples, PUSH_LIMIT is used to enable limit pushing for join rewriting.
-- Assume t1 is an existing table structure.
CREATE TABLE t1(c1 int primary key, c2 int, c3 int);
-- Query example using the PUSH_LIMIT hint
SELECT /*+push_limit*/ *
FROM t1 a LEFT JOIN t1 b on a.c2 = b.c2
fetch next 10 rows only;
NO PUSH_LIMIT Hint
The NO PUSH_LIMIT hint prevents the optimizer from altering the original query structure, especially when you do not want LIMIT pushdown to occur within a join query. Its reverse operation is the PUSH_LIMIT hint.
Syntax
/*+ NO_PUSH_LIMIT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, NO PUSH_LIMIT is used to prevent limit pushdown during join rewriting.
-- Assume t1 is an existing table structure.
CREATE TABLE t1(c1 int primary key, c2 int, c3 int);
-- Query example using the NO PUSH_LIMIT hint
SELECT /*+no_push_limit*/ *
FROM t1 a LEFT JOIN t1 b ON a.c2 = b.c2
FETCH NEXT 10 ROWS ONLY;
REPLACE_CONST Hint
The REPLACE_CONST hint enables the optimizer to perform constant propagation query rewriting, which replaces equivalent conditions in a query with constants to reduce complexity and improve query efficiency. Its reverse operation is the NO_REPLACE_CONST hint.
Syntax
/*+ REPLACE_CONST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, REPLACE_CONST is used to enable constant propagation query rewriting.
-- Assume that t1 is the existing table.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Queries with the REPLACE_CONST hint propagate simplified WHERE clauses
SELECT /*+replace_const*/ c2
FROM t1
WHERE c1 = c2 AND c2 = 4;
NO_REPLACE_CONST Hint
The NO_REPLACE_CONST hint prohibits constant propagation query rewriting to avoid unnecessary rewrites that may occur in certain scenarios. Its reverse operation is the REPLACE_CONST hint.
Syntax
/*+ NO_REPLACE_CONST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, NO_REPLACE_CONST is used to prohibit constant propagation query rewrite.
-- Assume that t1 is the existing table.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Query example using the NO_REPLACE_CONST hint to maintain the original query conditions unchanged
SELECT /*+no_replace_const*/ c2
FROM t1
WHERE c1 = c2 AND c2 = 4;
SIMPLIFY_DISTINCT Hint
The SIMPLIFY_DISTINCT hint enables the optimizer to rewrite distinct queries, simplifying and optimizing the processing of such queries. Its counterpart is the NO_SIMPLIFY_DISTINCT hint.
Syntax
/*+ SIMPLIFY_DISTINCT [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, SIMPLIFY_DISTINCT is used to enable a series of distinct-related query rewrite.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
SELECT DISTINCT c1, c2
FROM (SELECT /*+simplify_distinct*/ DISTINCT t1.* FROM t1);
NO_SIMPLIFY_DISTINCT Hint
The NO_SIMPLIFY_DISTINCT hint explicitly prohibits the simplification of DISTINCT queries. This can preserve the original query in specific scenarios and prevent the optimizer from automatically performing rewrites that may not be desired. Its counterpart is the SIMPLIFY_DISTINCT hint.
Syntax
/*+ NO_SIMPLIFY_DISTINCT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, NO_SIMPLIFY_DISTINCT is used to prohibit a series of distinct-related query rewrite.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
SELECT DISTINCT c1, c2
FROM (SELECT /*+no_simplify_distinct*/ DISTINCT t1.* FROM t1);
SIMPLIFY_EXPR Hint
The SIMPLIFY_EXPR hint enables the optimizer to transform and simplify expressions in a query. Its reverse operation is the NO_SIMPLIFY_EXPR hint.
Syntax
/*+ SIMPLIFY_EXPR [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, SIMPLIFY_expr is used to enable the optimizer to transform and simplify expressions in the query.
-- Use the `SIMPLIFY_expr` hint to optimize query efficiency by removing redundant logic during condition simplification.
SELECT /*+simplify_expr*/ 1 FROM t1 WHERE c1 = 1 OR 1 = 1;
NO_SIMPLIFY_EXPR Hint
The NO_SIMPLIFY_EXPR hint disables the optimizer from transforming and simplifying expressions in a query. Its reverse operation is the SIMPLIFY_EXPR hint.
Syntax
/*+ NO_SIMPLIFY_EXPR [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, NO_SIMPLIFY_expr is used to prevent the optimizer from transforming and simplifying expressions in the query.
--Use the `NO_SIMPLIFY_EXPR` hint to prohibit expression conversion and simplification
SELECT /*+no_simplify_expr*/ 1 FROM t1 WHERE c1 = 1 OR 1 = 1;
SIMPLIFY_GROUP_BY Hint
The SIMPLIFY_GROUP_BY hint is used to optimize queries containing a GROUP BY clause. This hint enables the query optimizer to simplify and rewrite GROUP BY operations. Its counterpart is the NO_SIMPLIFY_GROUP_BY hint.
Syntax
/*+ SIMPLIFY_GROUP_BY [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, SIMPLIFY_GROUP_BY is used to enable Group By-related query rewriting.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The `SIMPLIFY_GROUP_BY` hint enables the optimizer to optimize the execution of aggregate operations through rewriting.
SELECT /*+simplify_group_by*/ COUNT(DISTINCT c1) FROM t1 GROUP BY c2;
NO_SIMPLIFY_GROUP_BY Hint
The NO_SIMPLIFY_GROUP_BY hint prohibits any query rewrite related to GROUP BY to maintain the original logic of the query. Its counterpart is the SIMPLIFY_GROUP_BY hint.
Syntax
/*+ NO_SIMPLIFY_GROUP_BY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, NO_SIMPLIFY_GROUP_BY is used to preserve the original GROUP BY logic and prevent any automated query optimization.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
SELECT /*+no_simplify_group_by*/ COUNT(DISTINCT c1) FROM t1 GROUP BY c2;
SIMPLIFY_LIMIT Hint
The SIMPLIFY_LIMIT hint enables the optimizer to simplify the output of queries with a LIMIT clause, improving the performance of paginated queries. Its counterpart is the NO_SIMPLIFY_LIMIT hint.
Syntax
/*+ SIMPLIFY_LIMIT [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, SIMPLIFY_LIMIT is used to enable rewrite of limit-related queries.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- You should use `SIMPLIFY_LIMIT` instead of `NO_SIMPLIFY_LIMIT`. Correct the Hint usage in the example.
SELECT /*+simplify_limit*/ *
FROM (SELECT /*+no_rewrite*/ ROWNUM cnt,
(SELECT c2 FROM t1 WHERE c1 = 1)
FROM t1)
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
NO_SIMPLIFY_LIMIT Hint
The NO_SIMPLIFY_LIMIT hint is used to avoid query rewrite for the LIMIT clause. Its counterpart is the SIMPLIFY_LIMIT hint.
Syntax
/*+ NO_SIMPLIFY_LIMIT [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, NO_SIMPLIFY_LIMIT is used to prohibit LIMIT query rewrite.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Use `NO_SIMPLIFY_LIMIT` to ensure the original pagination logic remains unchanged.
SELECT /*+no_simplify_limit*/ *
FROM (SELECT /*+no_rewrite*/ ROWNUM cnt,
(SELECT c2 FROM t1 WHERE c1 = 1)
FROM t1)
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
SIMPLIFY_ORDER_BY Hint
The SIMPLIFY_ORDER_BY hint is used to optimize queries containing ORDER BY clauses. This hint enables the query optimizer to simplify and rewrite ORDER BY operations. Its counterpart is the NO_SIMPLIFY_ORDER_BY hint.
Syntax
/*+ SIMPLIFY_ORDER_BY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, SIMPLIFY_GROUP_BY is used to enable Group By-related query rewriting.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The `SIMPLIFY_ORDER_BY` hint enables the optimizer to optimize the execution of sorting operations through rewriting.
SELECT /*+simplify_order_by*/ c1 FROM
(SELECT /*+no_merge*/ * FROM t1 ORDER BY c2) ORDER BY c2 DESC;
NO_SIMPLIFY_ORDER_BY Hint
The NO_SIMPLIFY_ORDER_BY hint prohibits any ORDER BY-related query rewriting to maintain the original logic of the query. Its counterpart is the SIMPLIFY_ORDER_BY hint.
Syntax
/*+ NO_SIMPLIFY_ORDER_BY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, NO_SIMPLIFY_ORDER_BY is used to preserve the original ORDER BY logic and prevent any automated query optimization.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
SELECT /*+no_simplify_order_by*/ c1 FROM
(SELECT /*+no_merge*/ * FROM t1 ORDER BY c2) ORDER BY c2 DESC;
SIMPLIFY_SUBQUERY Hint
The SIMPLIFY_SUBQUERY hint enables rewriting of nested subqueries, helping to transform or eliminate unnecessary subquery structures to simplify the query logic. Its counterpart is the NO_SIMPLIFY_SUBQUERY hint.
Syntax
/*+ SIMPLIFY_SUBQUERY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, SIMPLIFY_SUBQUERY is used to enable subquery-related query rewriting.
-- Assume that t1 is a pre-created table.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The query example demonstrates the use of the `SIMPLIFY_SUBQUERY` hint to enable the optimizer to simplify or eliminate unnecessary subquery parts.
SELECT /*+simplify_subquery*/ * FROM t1
WHERE EXISTS (SELECT * FROM t1);
NO_SIMPLIFY_SUBQUERY Hint
The NO_SIMPLIFY_SUBQUERY hint prevents the automatic rewriting of subqueries, preserving the original query structure and logic. Its reverse operation is the SIMPLIFY_SUBQUERY hint.
Syntax
/*+ NO_SIMPLIFY_SUBQUERY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, the NO_SIMPLIFY_SUBQUERY hint is used to prohibit query rewriting related to subqueries.
-- Assume that t1 is a pre-created table.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The query example demonstrates the use of the `NO_SIMPLIFY_SUBQUERY` hint to ensure that a subquery is kept intact and not rewritten by the optimizer.
SELECT /*+no_simplify_subquery*/ * FROM t1
WHERE EXISTS (SELECT * FROM t1);
SIMPLIFY_SET Hint
The SIMPLIFY_SET hint enables the optimizer to rewrite set operations (such as UNION, INTERSECT, and EXCEPT). Its reverse operation is the NO_SIMPLIFY_SET hint.
Syntax
/*+ SIMPLIFY_SET [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Note
The Query Block of a set query does not have a direct location to add hints. Therefore, you must specify the Query Block for which the hint takes effect using the QB Name method.
Examples
In the query example below, the SIMPLIFY_SET hint is used to enable query rewriting related to set queries.
-- Assume that t1 is a pre-existing table.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Optimize queries by using the SIMPLIFY_SET hint
SELECT /*+simplify_set(@set$1)*/ * FROM t1
UNION ALL
SELECT * FROM t1 WHERE 1 = 0;
The preceding example contains a hint (/*+simplify_set(@set$1)*/), which instructs the database optimizer not to automatically simplify this specific set operation (UNION ALL) query. @set$1 is a Query Block Name (QB Name), which, in this case, identifies the entire UNION ALL query statement.
NO_SIMPLIFY_SET Hint
The NO_SIMPLIFY_SET hint disables query rewriting related to set queries. Its reverse operation is the SIMPLIFY_SET hint.
Syntax
/*+ NO_SIMPLIFY_SET [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint is applied.
Note
Examples
In the query example below, NO_SIMPLIFY_SET is used to prohibit query rewrite related to set queries.
-- For the predefined table t1
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Use the NO_SIMPLIFY_SET hint to prevent set query rewrite
SELECT /*+no_simplify_set(@set$1)*/ * FROM t1
UNION ALL
SELECT * FROM t1 WHERE 1 = 0;
The preceding example contains a hint (/*+no_simplify_set(@set$1)*/), which instructs the database optimizer not to automatically simplify this specific set operation (UNION ALL) query. @set$1 is a Query Block Name (QB Name), which, in this case, identifies the entire UNION ALL query statement.
SIMPLIFY_WINFUNC Hint
The SIMPLIFY_WINFUNC hint enables the optimizer to perform effective rewriting of queries containing window functions. Its counterpart is the NO_SIMPLIFY_WINFUNC hint.
Syntax
/*+ SIMPLIFY_WINFUNC [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, SIMPLIFY_WINFUNC is used to enable query rewrite for window functions.
-- The prerequisite is that table t1 has been created.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The following query example uses the SIMPLIFY_WINFUNC hint to enable the optimizer to identify and apply any possible rewrite that simplifies the computation of MAX(c2) OVER (PARTITION BY c1).
SELECT /*+simplify_winfunc*/ MAX(c2) OVER (PARTITION BY c1) FROM t1;
NO_SIMPLIFY_WINFUNC Hint
The NO_SIMPLIFY_WINFUNC hint prohibits the optimizer from rewriting queries containing window functions. Use it when you want to ensure that specific logic is preserved exactly as written. Its counterpart is the SIMPLIFY_WINFUNC hint.
Syntax
/*+ NO_SIMPLIFY_WINFUNC [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, NO_SIMPLIFY_WINFUNC is used to prohibit query rewriting related to window functions.
-- Assume table t1 already exists.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Queries with the NO_SIMPLIFY_WINFUNC hint retain the original behavior of window functions
SELECT /*+no_simplify_winfunc*/ MAX(c2) OVER (PARTITION BY c1) FROM t1;
UNNEST Hint
The UNNEST hint enables the optimizer to decompose subqueries into joins to improve query rewrite, transforming subqueries encountered in the query into various joins. Its reverse operation is the NO_UNNEST hint.
OceanBase AI Database supports further subquery optimization through hints such as AGGR_FIRST_UNNEST and JOIN_FIRST_UNNEST, which instruct the optimizer to prioritize aggregation or join operations during subquery unnesting. Nevertheless, in most scenarios, it is recommended to use the UNNEST hint directly, as it provides a more general optimization strategy.
Syntax
/*+ UNNEST [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint is applied.
Examples
In the query example below, UNNEST is used to enable subquery lifting for related query rewrite.
-- Assume table t1 has been created.
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
-- Queries using the UNNEST hint trigger subquery optimization
SELECT (SELECT /*+unnest*/ COUNT(*)
FROM t1 b
WHERE a.c2 = b.c2)
FROM t1 a;
NO_UNNEST Hint
The NO_UNNEST hint prevents query rewrite that promotes subqueries, even when the subquery can be effectively rewritten as a join. Its counterpart is the UNNEST hint.
Syntax
/*+ NO_UNNEST [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
In the query example below, NO_UNNEST is used to prohibit subquery lifting and related query rewriting.
-- The table t1 has been created.
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
-- Apply the NO_UNNEST hint to prevent subqueries from being automatically rewritten into join operations.
SELECT (SELECT /*+no_unnest*/ COUNT(*)
FROM t1 b
WHERE a.c2 = b.c2)
FROM t1 a;