In SQL queries, heuristics are used to quickly find a feasible solution, which may not be the optimal one, especially in complex queries. The database optimizer tries various rules and transformation strategies to find the most efficient execution plan.
| Hint type | Description |
|---|---|
AGGR_FIRST_UNNEST |
When a query contains aggregate subqueries (such as AVG, SUM, MAX, and so on), the AGGR_FIRST_UNNEST hint enables aggregate-first subquery rewriting. Its reverse operation is NO_AGGR_FIRST_UNNEST. |
NO_AGGR_FIRST_UNNEST |
Disables aggregate-first subquery rewriting. Its reverse operation is AGGR_FIRST_UNNEST. |
COUNT_TO_EXISTS |
Enables rewriting of COUNT subqueries as EXISTS subqueries. Its reverse operation is NO_COUNT_TO_EXISTS. |
NO_COUNT_TO_EXISTS |
Disables rewriting of COUNT subqueries as EXISTS subqueries. Its reverse operation is COUNT_TO_EXISTS. |
ELIMINATE_JOIN |
Identifies and removes unnecessary join operations in the query execution plan. Its reverse operation is NO_ELIMINATE_JOIN. |
NO_ELIMINATE_JOIN |
Does not eliminate join operations in the query execution plan. Its reverse operation is ELIMINATE_JOIN. |
FAST_MINMAX |
Enables rewriting of queries for MIN or MAX optimization. Its reverse operation is NO_FAST_MINMAX. |
NO_FAST_MINMAX |
Disables rewriting of queries for MIN or MAX optimization. Its reverse operation is FAST_MINMAX. |
INLINE |
Instructs the optimizer to expand common table expressions (CTEs) and embed their query content directly into the main query. Its reverse operation is MATERIALIZE. |
MATERIALIZE |
Controls whether to materialize or not to expand CTEs. Its reverse operation is INLINE. |
JOIN_FIRST_UNNEST |
When processing subqueries with aggregate functions, the optimizer attempts to prioritize join operations for query rewriting. Its reverse operation is NO_JOIN_FIRST_UNNEST. |
NO_JOIN_FIRST_UNNEST |
Disables the join-priority rewriting strategy in aggregate subqueries. Its reverse operation is JOIN_FIRST_UNNEST. |
LEFT_TO_ANTI |
Enables the optimizer to attempt converting outer joins (OUTER JOIN) to 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) to anti joins (ANTI JOIN). Its reverse operation is LEFT_TO_ANTI. |
OUTER_TO_INNER |
Enables the optimizer to rewrite outer joins (OUTER JOIN) as inner joins (INNER JOIN) when possible. Its reverse operation is NO_OUTER_TO_INNER. |
NO_OUTER_TO_INNER |
Disables the optimizer from rewriting outer joins (OUTER JOIN) as inner joins (INNER JOIN). Its reverse operation is OUTER_TO_INNER. |
PRED_DEDUCE |
Enables the optimizer to generate new predicates based on existing ones, optimizing query execution through derivation and pushing down. This rewriting is typically effective for nested or joined queries. Its reverse operation is NO_PRED_DEDUCE. |
NO_PRED_DEDUCE |
Disables the optimizer from generating new predicates based on existing ones. Its reverse operation is PRED_DEDUCE. |
PROJECT_PRUNE |
Instructs the optimizer to prune unused projected columns in internal query blocks. This is particularly useful in queries with multiple table joins and nested queries. Its reverse operation is NO_PROJECT_PRUNE. |
NO_PROJECT_PRUNE |
Prevents the optimizer from pruning unused projected columns in internal query blocks. Its reverse operation is PROJECT_PRUNE. |
PULLUP_EXPR |
Enables expression pulling rewriting. Its reverse operation is NO_PULLUP_EXPR. |
NO_PULLUP_EXPR |
Disables expression pulling rewriting. Its reverse operation is PULLUP_EXPR. |
PUSH_LIMIT |
Enables limit pushing rewriting. Its reverse operation is NO_PUSH_LIMIT. |
NO_PUSH_LIMIT |
Disables limit pushing rewriting. Its reverse operation is PUSH_LIMIT. |
REPLACE_CONST |
Enables constant propagation rewriting. Its reverse operation is NO_REPLACE_CONST. |
NO_REPLACE_CONST |
Disables constant propagation rewriting. Its reverse operation is REPLACE_CONST. |
SIMPLIFY_DISTINCT |
Enables a series of DISTINCT-related query rewritings. Its reverse operation is NO_SIMPLIFY_DISTINCT. |
NO_SIMPLIFY_DISTINCT |
Disables a series of DISTINCT-related query rewritings. Its reverse operation is SIMPLIFY_DISTINCT. |
SIMPLIFY_EXPR |
Enables expression conversion-related query rewritings. Its reverse operation is NO_SIMPLIFY_EXPR. |
NO_SIMPLIFY_EXPR |
Disables expression conversion-related query rewritings. Its reverse operation is SIMPLIFY_EXPR. |
SIMPLIFY_GROUP_BY |
Enables GROUP BY-related query rewritings. Its reverse operation is NO_SIMPLIFY_GROUP_BY. |
NO_SIMPLIFY_GROUP_BY |
Disables GROUP BY-related query rewritings. Its reverse operation is SIMPLIFY_GROUP_BY. |
SIMPLIFY_LIMIT |
Enables LIMIT-related query rewritings. Its reverse operation is NO_SIMPLIFY_LIMIT. |
NO_SIMPLIFY_LIMIT |
Disables LIMIT-related query rewritings. Its reverse operation is SIMPLIFY_LIMIT. |
SIMPLIFY_ORDER_BY |
Enables ORDER BY-related query rewritings. Its reverse operation is NO_SIMPLIFY_ORDER_BY. |
NO_SIMPLIFY_ORDER_BY |
Disables ORDER BY-related query rewritings. Its reverse operation is SIMPLIFY_ORDER_BY. |
SIMPLIFY_SET |
Enables set query-related query rewritings. Its reverse operation is NO_SIMPLIFY_SET. |
NO_SIMPLIFY_SET |
Disables query rewriting for set operations. Its opposite is SIMPLIFY_SET. |
SIMPLIFY_SUBQUERY |
Enables query rewriting for subqueries. Its opposite is NO_SIMPLIFY_SUBQUERY. |
NO_SIMPLIFY_SUBQUERY |
Disables query rewriting for subqueries. Its opposite is SIMPLIFY_SUBQUERY. |
SIMPLIFY_WINFUNC |
Enables query rewriting for window functions. Its opposite is NO_SIMPLIFY_WINFUNC. |
NO_SIMPLIFY_WINFUNC |
Disables query rewriting for window functions. Its opposite is SIMPLIFY_WINFUNC. |
UNNEST |
Enables subquery unnesting. Its opposite is NO_UNNEST. |
NO_UNNEST |
Disables subquery unnesting. Its opposite is UNNEST. |
AGGR_FIRST_UNNEST Hint
The AGGR_FIRST_UNNEST hint makes the optimizer prioritize independent aggregation calculations over merging them into the main query when handling subqueries with aggregate functions. Its reverse operation is the NO_AGGR_FIRST_UNNEST hint.
It is not recommended to directly use the AGGR_FIRST_UNNEST or NO_AGGR_FIRST_UNNEST hints because they directly affect the rewriting behavior of subquery lifting. Generally, if you need more basic control over subquery lifting behavior, it is recommended to use the UNNEST or NO_UNNEST hints instead.
Syntax
/*+ AGGR_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
In the following example, the AGGR_FIRST_UNNEST hint is used to enable aggregation in subqueries, prioritizing subquery lifting during query rewriting.
-- The hint directs the optimizer to prioritize lifting the AVG aggregate function from the subquery 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 prevents the optimizer from prioritizing independent aggregation calculations when handling subqueries with aggregate functions. Its reverse operation is the AGGR_FIRST_UNNEST hint.
It is not recommended to directly use the AGGR_FIRST_UNNEST or NO_AGGR_FIRST_UNNEST hints because they directly affect the rewriting behavior of subquery lifting. Generally, if you need more basic control over subquery lifting behavior, it is recommended to use the UNNEST or NO_UNNEST hints instead.
Syntax
/*+ NO_AGGR_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
In the following example, the NO_AGGR_FIRST_UNNEST hint is used, keeping the original form of the subquery without prioritizing aggregation during query rewriting.
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 query rewriting that converts COUNT subqueries into EXISTS subqueries. Its reverse operation is the NO_COUNT_TO_EXISTS hint.
Syntax
/*+ COUNT_TO_EXISTS [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
qb_name_list: Optional. Specifies which query blocks to rewrite. If not specified, the hint applies to all subqueries.
Examples
In the following example, the COUNT_TO_EXISTS hint is used to enable query rewriting that converts COUNT subqueries into EXISTS subqueries.
-- The `COUNT_TO_EXISTS` hint directs the optimizer to convert the inner `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 NO_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 the hint applies.qb_name_list: Optional. Specifies which query blocks to exclude from rewriting. If not specified, the hint applies to all subqueries.
Examples
In the following example, the NO_COUNT_TO_EXISTS hint is used to disable query rewriting that converts COUNT subqueries into EXISTS subqueries.
-- The `NO_COUNT_TO_EXISTS` hint keeps the original `COUNT` subquery unchanged.
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 rewriting that eliminates joins. 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 the hint applies.table_list: Optional. Specifies which join tables to eliminate. If not specified, the hint applies to all join tables.
Examples
In the following example, the ELIMINATE_JOIN hint is used to enable query rewriting that eliminates joins.
-- The `ELIMINATE_JOIN` hint is used to find and eliminate the self-join on 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 rewriting that eliminates joins, retaining all specified joins. Its reverse operation is the ELIMINATE_JOIN hint.
Syntax
/*+ NO_ELIMINATE_JOIN [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.table_list: Optional. Specifies which join tables to exclude from elimination. If not specified, the hint applies to all join tables.
Examples
In the following example, the NO_ELIMINATE_JOIN hint is used to disable query rewriting that eliminates joins.
-- The `NO_ELIMINATE_JOIN` hint ensures that the join operation is 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 and MAX aggregate functions. It instructs the query optimizer to rewrite the query for faster retrieval of minimum and maximum values. The reverse operation is the NO_FAST_MINMAX hint.
Syntax
/*+ FAST_MINMAX [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
The following example shows how to use the FAST_MINMAX hint to enable query rewriting for MIN and MAX optimization.
CREATE TABLE t1(c1 int primary key, c2 int, c3 int);
-- Use the `FAST_MINMAX` hint to calculate MAX(c1) as quickly as possible.
SELECT /*+ FAST_MINMAX */ MAX(c1) FROM t1;
NO_FAST_MINMAX Hint
The NO_FAST_MINMAX hint disables query rewriting for queries using the MIN and MAX aggregate functions. The reverse operation is the FAST_MINMAX hint.
Syntax
/*+ NO_FAST_MINMAX [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
The following example shows how to use the NO_FAST_MINMAX hint to disable query rewriting for MIN and MAX optimization.
CREATE TABLE t1(c1 int primary key, c2 int, c3 int);
-- Use the `NO_FAST_MINMAX` hint to prevent the optimizer from accelerating the MAX(c1) operation.
SELECT /*+ NO_FAST_MINMAX */ MAX(c1) FROM t1;
INLINE Hint
The INLINE hint specifies to expand a common table expression (CTE) and integrate it directly into the main query. The reverse operation is the MATERIALIZE hint.
Syntax
/*+ INLINE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
The following example shows how to use the INLINE hint to expand the CTE defined in with cte.
-- Use the `INLINE` hint to directly use the subquery content defined in with cte in 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 materialize or not to expand a common table expression (CTE). The reverse operation is the INLINE hint.
Syntax
/*+ MATERIALIZE [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.qb_name_list: Optional. Specifies the query blocks to prevent from being materialized. If not specified, no CTE is materialized. It is recommended to use theMATERIALIZEhint only within CTEs defined inwith cte.
Examples
The following examples show how to use the MATERIALIZE hint to prevent the expansion of CTEs defined in with cte or to actively materialize CTEs for 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 rewriting for aggregate subquery lifting by prioritizing the use of join operations to optimize subqueries containing aggregate functions. The reverse operation is the NO_JOIN_FIRST_UNNEST hint.
Note: It is not recommended to directly use the JOIN_FIRST_UNNEST or NO_JOIN_FIRST_UNNEST hints. Instead, use the UNNEST or NO_UNNEST hints to influence subquery lifting.
Syntax
/*+ JOIN_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
The following example shows how to use the JOIN_FIRST_UNNEST hint to enable query rewriting for aggregate subqueries by prioritizing join operations.
-- Use the `JOIN_FIRST_UNNEST` hint to prioritize join operations in the execution plan when handling subqueries containing aggregate functions (such as AVG).
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 disables query rewriting for aggregate subquery lifting by preventing the prioritization of join operations. Using this hint, the query optimizer will not consider join-based strategies. The reverse operation is the JOIN_FIRST_UNNEST hint.
Note: It is not recommended to directly use the JOIN_FIRST_UNNEST or NO_JOIN_FIRST_UNNEST hints. Instead, use the UNNEST or NO_UNNEST hints to influence subquery lifting.
Syntax
/*+ NO_JOIN_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
The following example shows how to use the NO_JOIN_FIRST_UNNEST hint to disable query rewriting for aggregate subqueries by preventing the prioritization of join operations.
-- Use the `NO_JOIN_FIRST_UNNEST` hint to prevent the optimizer from prioritizing join operations when handling subqueries.
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 rewrite that converts OUTER JOIN to ANTI JOIN. 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. Specifies the right table of a specificLEFT JOINto which this hint applies. If this parameter is omitted, this hint applies to allOUTER JOINoperations.
Examples
The following query example uses the LEFT_TO_ANTI hint to enable the query rewrite that converts OUTER JOIN to 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 /*+ 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 rewrite that converts OUTER JOIN to ANTI JOIN. 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
The following query example uses the NO_LEFT_TO_ANTI hint to disable the query rewrite that converts OUTER JOIN to 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 enables the query rewrite that converts OUTER JOIN to INNER JOIN. Its reverse operation is the NO_OUTER_TO_INNER hint.
Syntax
/*+ OUTER_TO_INNER [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
The following query example uses the OUTER_TO_INNER hint to enable the query rewrite that converts OUTER JOIN to INNER JOIN.
-- The WHERE condition b.c1 > 0 applies only to table b and implicitly excludes the possibility of null values in the right table of the outer join, making the outer join operation behave like 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 the query rewrite that converts OUTER JOIN to INNER JOIN. Its reverse operation is the OUTER_TO_INNER hint.
Syntax
/*+ NO_OUTER_TO_INNER [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
The following query example uses the NO_OUTER_TO_INNER hint to disable the query rewrite that converts OUTER JOIN to INNER JOIN.
-- The WHERE condition b.c1 > 0 applies only to table b and implicitly excludes the possibility of null values in the right table of the outer join, making the outer join operation behave like an inner join.
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 enables the query rewrite that derives new predicates from existing ones and pushes them down. This rewrite is effective for nested queries and joins. Its reverse operation is the NO_PRED_DEDUCE hint.
Syntax
/*+ PRED_DEDUCE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
The following query example uses the PRED_DEDUCE hint to enable the query rewrite that derives new predicates from existing ones and pushes them down.
-- Enable predicate deduction to rewrite the outer query.
select /*+pred_deduce*/ c1
from (
-- Disable subquery merging to ensure the no_merge hint is effective.
select /*+no_merge*/ t1.* from t1
)
where c1 = 3;
NO_PRED_DEDUCE Hint
The NO_PRED_DEDUCE hint disables the query rewrite that derives new predicates from existing ones and pushes them down. Its reverse operation is the PRED_DEDUCE hint.
Syntax
/*+ NO_PRED_DEDUCE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
The following query example uses the NO_PRED_DEDUCE hint to disable the query rewrite that derives new predicates from existing ones and pushes them down.
-- Disable predicate deduction to rewrite the outer query.
select /*+no_pred_deduce*/ c1
from (
-- Disable subquery merging to ensure the no_merge hint is effective.
select /*+no_merge*/ t1.* from t1
)
where c1 = 3;
PROJECT_PRUNE Hint
The PROJECT_PRUNE hint optimizes query execution by reducing the amount of data processed, especially in large datasets and complex queries. It prunes unnecessary columns to reduce the output of query blocks. This query rewrite technique, known as Project Pruning, improves 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 the particular query block to which thePROJECT_PRUNEhint applies, providing finer-grained control for query optimization.
Examples
The following query example uses the PROJECT_PRUNE hint to enable the Project Pruning rewrite mechanism. This causes the optimizer to prune all output columns of the inline view except for the C1 column.
select c1 from (select /*+no_merge project_prune*/ t1.* from t1);
NO_PROJECT_PRUNE Hint
The NO_PROJECT_PRUNE hint disables the query rewrite operation of project pruning. Project pruning automatically trims unused output columns during query optimization. When it is necessary to retain these columns for specific performance or functional requirements, the NO_PROJECT_PRUNE hint becomes particularly important. Its reverse operation is the PROJECT_PRUNE hint.
Syntax
/*+ NO_PROJECT_PRUNE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies which query block should avoid applying project pruning. This provides more precise control when multiple query blocks exist.
Examples
In the following query example, the NO_PROJECT_PRUNE hint is used to disable project pruning rewrite. This setting ensures that even if all output columns except c1 are not needed, these columns will still remain in the result of the inline view.
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 (promote) subquery expressions to the parent query level. Its reverse operation is the NO_PULLUP_EXPR hint.
Syntax
/*+ PULLUP_EXPR [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint.
Examples
In the following query example, the PULLUP_EXPR hint is used to enable expression pulling up rewrite. After the expression pulling up query rewrite is triggered, the subquery in the SELECT clause of the inline view is pulled up to the outer level.
-- Assume that t1 is an existing table
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 optimizer from performing expression pulling up optimization. Its reverse operation is the PULLUP_EXPR hint.
Syntax
/*+ NO_PULLUP_EXPR [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint.
Examples
In the following query example, the NO_PULLUP_EXPR hint is used to disable expression pulling up rewrite. After the expression pulling up query rewrite is triggered, the subquery in the SELECT clause of the inline view is prevented from being pulled up to the outer level.
-- Assume that t1 is an existing table
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 allows the optimizer to push down (lower) the LIMIT clause (which restricts the number of returned rows) into the inner part of a join query, such as an outer join or a Cartesian product, significantly improving the efficiency of paginated queries. Its reverse operation is the NO_PUSH_LIMIT hint.
Syntax
/*+ PUSH_LIMIT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint.
Examples
In the following query example, the PUSH_LIMIT hint is used to enable limit pushing down join rewrite.
-- Assume that t1 is an existing table
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 limit pushing down to the inner part of a join query is not desired. Its reverse operation is the PUSH_LIMIT hint.
Syntax
/*+ NO_PUSH_LIMIT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint.
Examples
In the following query example, the NO_PUSH_LIMIT hint is used to disable limit pushing down join rewrite.
-- Assume that t1 is an existing table
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 rewrite, which involves replacing equality conditions in the 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 apply the hint.
Examples
In the following query example, the REPLACE_CONST hint is used to enable constant propagation query rewrite.
-- Assume that t1 is an existing table
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Query using the REPLACE_CONST hint to simplify the WHERE clause
SELECT /*+replace_const*/ c2
FROM t1
WHERE c1 = c2 AND c2 = 4;
NO_REPLACE_CONST Hint
The NO_REPLACE_CONST hint disables constant propagation query rewrite to prevent unnecessary rewriting 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 apply the hint.
Examples
In the following query example, the NO_REPLACE_CONST hint is used to disable constant propagation query rewrite.
-- Assume that t1 is an existing table
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Query using the NO_REPLACE_CONST hint to maintain the original query conditions
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 and optimize queries with DISTINCT operations. Its reverse operation is the NO_SIMPLIFY_DISTINCT hint.
Syntax
/*+ SIMPLIFY_DISTINCT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following query example shows how to use the SIMPLIFY_DISTINCT hint to enable a series of query rewrites related to DISTINCT operations.
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 disables the optimizer from simplifying queries with DISTINCT operations. This can be useful in specific scenarios where you want to maintain the original query structure and prevent the optimizer from making unwanted rewrites. Its reverse operation is the SIMPLIFY_DISTINCT hint.
Syntax
/*+ NO_SIMPLIFY_DISTINCT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following query example shows how to use the NO_SIMPLIFY_DISTINCT hint to disable a series of query rewrites related to DISTINCT operations.
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 rewrite and simplify expressions in queries. Its reverse operation is the NO_SIMPLIFY_EXPR hint.
Syntax
/*+ SIMPLIFY_EXPR [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following query example shows how to use the SIMPLIFY_EXPR hint to enable the optimizer to rewrite and simplify expressions in queries.
-- 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 rewriting and simplifying expressions in queries. Its reverse operation is the SIMPLIFY_EXPR hint.
Syntax
/*+ NO_SIMPLIFY_EXPR [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following query example shows how to use the NO_SIMPLIFY_EXPR hint to disable the optimizer from rewriting and simplifying expressions in queries.
-- Use the `NO_SIMPLIFY_EXPR` hint to prevent expression rewriting and simplification.
SELECT /*+no_simplify_expr*/ 1 FROM t1 WHERE c1 = 1 OR 1 = 1;
SIMPLIFY_GROUP_BY Hint
The SIMPLIFY_GROUP_BY hint enables the optimizer to rewrite and simplify queries with GROUP BY clauses. Its reverse operation is the NO_SIMPLIFY_GROUP_BY hint.
Syntax
/*+ SIMPLIFY_GROUP_BY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following query example shows how to use the SIMPLIFY_GROUP_BY hint to enable query rewrites related to GROUP BY operations.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Use the `SIMPLIFY_GROUP_BY` hint to enable the optimizer to rewrite and simplify GROUP BY operations.
SELECT /*+simplify_group_by*/ COUNT(DISTINCT c1) FROM t1 GROUP BY c2;
NO_SIMPLIFY_GROUP_BY Hint
The NO_SIMPLIFY_GROUP_BY hint disables any query rewrites related to GROUP BY clauses to maintain the original query logic. Its reverse operation is the SIMPLIFY_GROUP_BY hint.
Syntax
/*+ NO_SIMPLIFY_GROUP_BY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following query example shows how to use the NO_SIMPLIFY_GROUP_BY hint to maintain the original GROUP BY logic and prevent any automated query optimizations.
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 rewrite and simplify queries with LIMIT clauses to improve the performance of pagination queries. Its reverse operation is the NO_SIMPLIFY_LIMIT hint.
Syntax
/*+ SIMPLIFY_LIMIT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following query example shows how to use the SIMPLIFY_LIMIT hint to enable query rewrites related to LIMIT operations.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Use the `SIMPLIFY_LIMIT` hint instead of the `NO_SIMPLIFY_LIMIT` hint to correct the 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 disables query rewrites related to LIMIT clauses. Its reverse operation is the SIMPLIFY_LIMIT hint.
Syntax
/*+ NO_SIMPLIFY_LIMIT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following query example shows how to use the NO_SIMPLIFY_LIMIT hint to disable query rewrites related to LIMIT operations.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Use the `NO_SIMPLIFY_LIMIT` hint 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
SIMPLIFY_ORDER_BY Hint is used to optimize queries that contain the ORDER BY clause. This hint enables the query optimizer to simplify and rewrite the ORDER BY operations. Its reverse operation 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 applies.
Examples
The following query example uses the SIMPLIFY_ORDER_BY hint to enable query rewriting for the ORDER BY clause.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The `SIMPLIFY_ORDER_BY` hint enables the optimizer to rewrite the query to optimize the execution of the ORDER BY operation.
SELECT /*+simplify_order_by*/ c1 FROM
(SELECT /*+no_merge*/ * FROM t1 ORDER BY c2) ORDER BY c2 DESC;
NO_SIMPLIFY_ORDER_BY Hint
NO_SIMPLIFY_ORDER_BY Hint disables any query rewriting related to the ORDER BY clause to preserve the original logic of the query. Its reverse operation 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 applies.
Examples
The following query example uses the NO_SIMPLIFY_ORDER_BY hint to keep the original ORDER BY logic and prevent any automatic 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
SIMPLIFY_SUBQUERY Hint enables the optimizer to rewrite nested subqueries, which helps to convert or eliminate unnecessary subquery structures to simplify the query logic. Its reverse operation is the NO_SIMPLIFY_SUBQUERY hint.
Syntax
/*+ SIMPLIFY_SUBQUERY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
The following query example uses the SIMPLIFY_SUBQUERY hint to enable query rewriting for 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 `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
NO_SIMPLIFY_SUBQUERY Hint prevents the optimizer from automatically rewriting 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
The following query example uses the NO_SIMPLIFY_SUBQUERY hint to disable query rewriting for 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 the subquery remains unchanged and is not rewritten by the optimizer.
SELECT /*+no_simplify_subquery*/ * FROM t1
WHERE EXISTS (SELECT * FROM t1);
SIMPLIFY_SET Hint
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
There is no direct way to add a hint to a set query's query block. Therefore, you must specify the query block to which the hint applies using the QB Name method.
Examples
The following query example uses the SIMPLIFY_SET hint to enable query rewriting for set queries.
-- Assume that t1 is a pre-created table.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Use the SIMPLIFY_SET hint to optimize the query.
SELECT /*+simplify_set(@set$1)*/ * FROM t1
UNION ALL
SELECT * FROM t1 WHERE 1 = 0;
In the example above, a hint (/*+simplify_set(@set$1)*/) is included. This hint disables the database optimizer from automatically simplifying the specific set operation (UNION ALL) query. @set$1 is a Query Block Name (QB Name), which in this case is used to identify the entire UNION ALL query statement.
NO_SIMPLIFY_SET Hint
NO_SIMPLIFY_SET Hint disables query rewriting for set queries. Its reverse operation is the SIMPLIFY_SET hint.
Syntax
/*+ NO_SIMPLIFY_SET [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Note
There is no direct way to add a hint to a set query's query block. Therefore, you must specify the query block to which the hint applies using the QB Name method.
Examples
The following query example uses the NO_SIMPLIFY_SET hint to disable query rewriting for set queries.
-- Assume that t1 is a pre-created table.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Use the NO_SIMPLIFY_SET hint to prevent set query rewriting.
SELECT /*+no_simplify_set(@set$1)*/ * FROM t1
UNION ALL
SELECT * FROM t1 WHERE 1 = 0;
In the example above, a hint (/*+no_simplify_set(@set$1)*/) is included. This hint disables the database optimizer from automatically simplifying the specific set operation (UNION ALL) query. @set$1 is a Query Block Name (QB Name), which in this case is used to identify the entire UNION ALL query statement.
SIMPLIFY_WINFUNC Hint
The SIMPLIFY_WINFUNC hint enables the optimizer to effectively rewrite queries containing window functions. Its reverse operation is the NO_SIMPLIFY_WINFUNC hint.
Syntax
/*+ SIMPLIFY_WINFUNC [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
The following query example uses the SIMPLIFY_WINFUNC hint to enable query rewriting for window function-related queries.
-- Assuming that the t1 table has been created
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The query with the SIMPLIFY_WINFUNC hint enables the optimizer to identify and implement any possible rewriting to simplify the calculation 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 prevents the optimizer from rewriting queries containing window functions, ensuring that special logic remains unchanged. Its reverse operation is the SIMPLIFY_WINFUNC hint.
Syntax
/*+ NO_SIMPLIFY_WINFUNC [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
The following query example uses the NO_SIMPLIFY_WINFUNC hint to disable query rewriting for window function-related queries.
-- Assuming that the t1 table has been created
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The query with the NO_SIMPLIFY_WINFUNC hint maintains the original behavior of the window function.
SELECT /*+no_simplify_winfunc*/ MAX(c2) OVER (PARTITION BY c1) FROM t1;
UNNEST Hint
The UNNEST hint enables the optimizer to rewrite queries by unnesting subqueries into joins. It converts subqueries in the query into various joins. Its reverse operation is the NO_UNNEST hint.
OceanBase Database supports further subquery unnesting optimizations, such as the AGGR_FIRST_UNNEST and JOIN_FIRST_UNNEST hints, which guide the optimizer to prioritize aggregation or join operations during subquery unnesting. However, in most scenarios, we recommend directly using the UNNEST hint, as it provides a general optimization strategy.
Syntax
/*+ UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
The following query example uses the UNNEST hint to enable query rewriting for subquery unnesting.
-- Assuming that the t1 table has been created
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
-- The query with the UNNEST hint triggers subquery unnesting.
SELECT (SELECT /*+unnest*/ COUNT(*)
FROM t1 b
WHERE a.c2 = b.c2)
FROM t1 a;
NO_UNNEST Hint
The NO_UNNEST hint prevents query rewriting by unnesting subqueries into joins, even when a subquery can be effectively rewritten into a join. Its reverse operation is the UNNEST hint.
Syntax
/*+ NO_UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this hint applies.
Examples
The following query example uses the NO_UNNEST hint to disable query rewriting for subquery unnesting.
-- Assuming that the t1 table has been created
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
-- The query with the NO_UNNEST hint prevents the subquery from being automatically rewritten into a join operation.
SELECT (SELECT /*+no_unnest*/ COUNT(*)
FROM t1 b
WHERE a.c2 = b.c2)
FROM t1 a;