In SQL queries, heuristics-related hints aim to quickly find feasible solutions, which may not necessarily be the optimal ones, especially in complex queries. The database optimizer attempts to apply 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 promotion query rewriting for aggregate subqueries. Its reverse operation is NO_AGGR_FIRST_UNNEST. |
NO_AGGR_FIRST_UNNEST |
Disables aggregate-first subquery promotion query rewriting for aggregate subqueries. Its reverse operation is AGGR_FIRST_UNNEST. |
COUNT_TO_EXISTS |
Enables query rewriting by converting COUNT subqueries to EXISTS subqueries. Its reverse operation is NO_COUNT_TO_EXISTS. |
NO_COUNT_TO_EXISTS |
Disables query rewriting by converting COUNT subqueries to 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 |
Prevents the elimination of join operations in the query execution plan. Its reverse 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 |
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 materialize CTEs. Its reverse operation is INLINE. |
JOIN_FIRST_UNNEST |
When processing subqueries with aggregate functions, it attempts to prioritize using join operations for query rewriting. Its reverse operation is NO_JOIN_FIRST_UNNEST. |
NO_JOIN_FIRST_UNNEST |
Prevents the application of join-priority rewriting strategies 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) to 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) to 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 inner query blocks. This is particularly useful in queries involving 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 inner 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 related to set queries. The reverse operation is SIMPLIFY_SET. |
SIMPLIFY_SUBQUERY |
Enables query rewriting related to subqueries. The reverse operation is NO_SIMPLIFY_SUBQUERY. |
NO_SIMPLIFY_SUBQUERY |
Disables query rewriting related to subqueries. The reverse operation is SIMPLIFY_SUBQUERY. |
SIMPLIFY_WINFUNC |
Enables query rewriting related to window functions. The reverse operation is NO_SIMPLIFY_WINFUNC. |
NO_SIMPLIFY_WINFUNC |
Disables query rewriting related to window functions. The reverse operation is SIMPLIFY_WINFUNC. |
UNNEST |
Enables subquery unnesting rewriting. The reverse operation is NO_UNNEST. |
NO_UNNEST |
Disables subquery unnesting rewriting. The reverse operation 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/NO_AGGR_FIRST_UNNEST hints because they directly affect the rewriting behavior of subquery lifting. Generally, if you need more fundamental 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
The following query example uses the AGGR_FIRST_UNNEST hint to enable aggregation for subqueries with aggregate functions, prioritizing subquery lifting in query rewriting.
-- The hint directs the optimizer to prioritize lifting the AVG aggregate function in 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/NO_AGGR_FIRST_UNNEST hints because they directly affect the rewriting behavior of subquery lifting. Generally, if you need more fundamental 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
The following query example uses the NO_AGGR_FIRST_UNNEST hint, which keeps the original form of the aggregate subquery and does not prioritize aggregation in 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 the query blocks to which the hint applies. If this parameter is omitted, the hint applies to all subqueries.
Examples
The following query example uses the COUNT_TO_EXISTS hint to enable query rewriting that converts COUNT subqueries into EXISTS subqueries.
-- The `COUNT_TO_EXISTS` hint directs the optimizer to convert nested `COUNT` subqueries 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 the query blocks to which the hint applies. If this parameter is omitted, the hint applies to all subqueries.
Examples
The following query example uses the NO_COUNT_TO_EXISTS hint 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 the join tables to which the hint applies. If this parameter is omitted, the hint applies to all join tables.
Examples
The following query example uses the ELIMINATE_JOIN hint to enable query rewriting that eliminates joins.
-- The `ELIMINATE_JOIN` hint identifies and eliminates 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 the join tables to which the hint applies. If this parameter is omitted, the hint applies to all join tables.
Examples
The following query example uses the NO_ELIMINATE_JOIN hint 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 database 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 query example uses the FAST_MINMAX hint to enable query rewriting for MIN/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 query example uses the NO_FAST_MINMAX hint to disable query rewriting for MIN/MAX optimization.
CREATE TABLE t1(c1 int primary key, c2 int, c3 int);
-- Use the `FAST_MINMAX` hint to disable the optimizer from accelerating MAX(c1) operations.
SELECT /*+ NO_FAST_MINMAX */ MAX(c1) FROM t1;
INLINE Hint
The INLINE hint specifies the expansion of a common table expression (CTE) and integrates the CTE 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 query example uses the INLINE hint to specify the expansion of 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 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 disable CTE expansion for. If not provided, CTE expansion is not performed. It is recommended to use theMATERIALIZEhint only within CTEs defined inwith cte.
Examples
The following query example uses the MATERIALIZE hint to specify not to expand the CTE defined in with cte or to materialize the CTE for specific query blocks.
-- 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, prioritizing join operations to optimize subqueries with aggregate functions. The reverse operation is the NO_JOIN_FIRST_UNNEST hint.
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 control subquery lifting.
Syntax
/*+ JOIN_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
The following query example uses the JOIN_FIRST_UNNEST hint to enable aggregate subquery lifting, prioritizing join operations for query rewriting.
-- Use the `JOIN_FIRST_UNNEST` hint to prioritize join operations in the execution plan when handling subqueries with 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 aggregate subquery lifting, preventing the query optimizer from prioritizing join operations. The reverse operation is the JOIN_FIRST_UNNEST hint.
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 control 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 query example uses the NO_JOIN_FIRST_UNNEST hint to disable aggregate subquery lifting, preventing the query optimizer from prioritizing join operations.
-- Use the `NO_JOIN_FIRST_UNNEST` hint to prevent the optimizer from prioritizing join operations when handling subqueries with aggregate functions (such as AVG).
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 query rewriting 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 the hint applies.table_list: Optional. Specifies the right table of a specificLEFT JOINfor query rewriting. If this parameter is omitted, the hint applies to allOUTER JOINoperations.
Examples
The following example uses the LEFT_TO_ANTI hint to enable query rewriting 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 query rewriting 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 the hint applies.
Examples
The following example uses the NO_LEFT_TO_ANTI hint to disable query rewriting 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 controls query rewriting that converts LEFT OUTER JOIN or RIGHT OUTER JOIN to a more efficient 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 the hint applies.
Examples
The following example uses the OUTER_TO_INNER hint to enable query rewriting that converts OUTER JOIN to INNER JOIN.
-- The filter condition b.c1 > 0 applies only to table b. It implicitly removes the possibility of null values in the right table of the outer join, making this outer join operation equivalent to 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 rewriting that converts LEFT OUTER JOIN or RIGHT OUTER JOIN to a more efficient INNER JOIN. The query optimizer does not convert outer joins to inner joins. 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 the hint applies.
Examples
The following example uses the NO_OUTER_TO_INNER hint to disable query rewriting that converts OUTER JOIN to INNER JOIN.
-- The `WHERE` clause condition may implicitly 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 query rewriting that derives new predicates from existing ones and pushes them down. This type of rewriting is typically effective for nested queries or joined queries. Its reverse operation is the NO_PRED_DEDUCE hint.
Syntax
/*+ PRED_DEDUCE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following example uses the PRED_DEDUCE hint to enable query rewriting that involves the outer query block in predicate derivation.
-- Enable predicate derivation to rewrite the outer query.
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 query optimizer from deriving new predicates from existing ones. Its reverse operation is the PRED_DEDUCE hint.
Syntax
/*+ NO_PRED_DEDUCE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following example uses the NO_PRED_DEDUCE hint to disable query rewriting that involves the outer query block in predicate derivation.
-- Disable predicate derivation to rewrite the outer query.
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, especially in large datasets and complex queries. It prunes unnecessary columns to reduce the output of query blocks (Query Blocks). This query rewriting 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 which specific query block should apply thePROJECT_PRUNEhint, providing more granular control over query optimization.
Examples
The following example uses the PROJECT_PRUNE hint to invoke the Project Pruning rewriting 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 removes unused output columns during query optimization. When you need to retain these columns for specific performance or functional requirements, the NO_PROJECT_PRUNE hint is particularly important. Its opposite 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 are present.
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, they 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 (lift) subquery expressions to the parent query level. Its opposite is the NO_PULLUP_EXPR hint.
Syntax
/*+ PULLUP_EXPR [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
In the following query example, the PULLUP_EXPR hint is used to enable expression lifting rewrite. After the expression lifting 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 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 optimizer from performing expression lifting optimization. Its opposite is the PULLUP_EXPR hint.
Syntax
/*+ NO_PULLUP_EXPR [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
In the following query example, the NO_PULLUP_EXPR hint is used to disable expression lifting rewrite. After the expression lifting 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 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 handling paginated data. It 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 opposite is the NO_PUSH_LIMIT hint.
Syntax
/*+ PUSH_LIMIT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
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 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 the LIMIT clause to be pushed down into the inner part of a join query. Its opposite is the PUSH_LIMIT hint.
Syntax
/*+ NO_PUSH_LIMIT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
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 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 rewrite, which involves replacing equality conditions in the query with constants to reduce complexity and improve query efficiency. Its opposite is the NO_REPLACE_CONST hint.
Syntax
/*+ REPLACE_CONST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
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 structure
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Query using the REPLACE_CONST hint to propagate and 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 opposite is the REPLACE_CONST hint.
Syntax
/*+ NO_REPLACE_CONST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
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 structure
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 involving the DISTINCT operation. Its reverse operation is the NO_SIMPLIFY_DISTINCT Hint.
Syntax
/*+ SIMPLIFY_DISTINCT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
The following query example uses the SIMPLIFY_DISTINCT Hint to enable a series of DISTINCT-related query rewrites.
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 DISTINCT queries. This can be useful in specific scenarios to maintain the original query structure and prevent unexpected rewrites. Its reverse operation is the SIMPLIFY_DISTINCT Hint.
Syntax
/*+ NO_SIMPLIFY_DISTINCT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
The following query example uses the NO_SIMPLIFY_DISTINCT Hint to disable a series of DISTINCT-related query rewrites.
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 convert 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 apply the hint to.
Examples
The following query example uses the SIMPLIFY_EXPR Hint to enable the optimizer to convert 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 converting 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 apply the hint to.
Examples
The following query example uses the NO_SIMPLIFY_EXPR Hint to disable the optimizer from converting and simplifying expressions in queries.
-- Use the `NO_SIMPLIFY_EXPR` Hint to prevent 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 the GROUP BY clause. This hint enables the query optimizer to simplify and rewrite GROUP BY operations. 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 apply the hint to.
Examples
The following query example uses the SIMPLIFY_GROUP_BY Hint to enable GROUP BY-related query rewrites.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Use the `SIMPLIFY_GROUP_BY` Hint to enable the optimizer to rewrite and optimize aggregate operations.
SELECT /*+simplify_group_by*/ COUNT(DISTINCT c1) FROM t1 GROUP BY c2;
NO_SIMPLIFY_GROUP_BY Hint
The NO_SIMPLIFY_GROUP_BY Hint prevents any GROUP BY-related query rewrites 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 apply the hint to.
Examples
The following query example uses the NO_SIMPLIFY_GROUP_BY Hint to maintain the original GROUP BY logic and prevent any automatic 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 simplify query outputs involving the LIMIT clause, improving 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 apply the hint to.
Examples
The following query example uses the SIMPLIFY_LIMIT Hint to enable LIMIT-related query rewrites.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- Use the `SIMPLIFY_LIMIT` Hint instead of `NO_SIMPLIFY_LIMIT` to 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 prevent query rewrites involving the LIMIT clause. Its reverse operation is the SIMPLIFY_LIMIT Hint.
Syntax
/*+ NO_SIMPLIFY_LIMIT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
The following query example uses the NO_SIMPLIFY_LIMIT Hint to disable LIMIT-related query rewrites.
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
The SIMPLIFY_ORDER_BY hint enables the query optimizer to simplify and rewrite queries that contain the ORDER BY clause. 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 the hint applies.
Examples
The following example shows how to use the SIMPLIFY_ORDER_BY hint to enable query rewriting for ORDER BY operations.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- `SIMPLIFY_ORDER_BY` Hint enables the optimizer to rewrite queries to optimize the execution of ORDER BY operations
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 prevents any query rewriting related to ORDER BY operations, preserving 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 the hint applies.
Examples
The following example shows how to use the NO_SIMPLIFY_ORDER_BY hint 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 the query optimizer to rewrite nested subqueries, helping to simplify or eliminate unnecessary subquery structures. Its reverse operation is the NO_SIMPLIFY_SUBQUERY hint.
Syntax
/*+ SIMPLIFY_SUBQUERY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.
Examples
The following example shows how to use 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);
-- This 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 query 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 the hint applies.
Examples
The following example shows how to use the NO_SIMPLIFY_SUBQUERY hint to prevent query rewriting for subqueries.
-- Assume that t1 is a pre-created table.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- This 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
The SIMPLIFY_SET hint enables the query 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 the 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 example shows how to use 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 indicates that the database optimizer should not automatically simplify 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
The NO_SIMPLIFY_SET hint disables query rewriting for set operations. Its reverse operation is the SIMPLIFY_SET hint.
Syntax
/*+ NO_SIMPLIFY_SET [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the 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 example shows how to use the NO_SIMPLIFY_SET hint to prevent query rewriting for set operations.
-- 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 indicates that the database optimizer should not automatically simplify 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 (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 apply the hint to.
Examples
The following query example uses the SIMPLIFY_WINFUNC hint to enable window function-related query rewriting.
-- Assuming that the t1 table 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 implement any possible rewrite 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 apply the hint to.
Examples
The following query example uses the NO_SIMPLIFY_WINFUNC hint to disable window function-related query rewriting.
-- Assuming that the t1 table has been created
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The following query example uses the NO_SIMPLIFY_WINFUNC hint to ensure that the window function's original behavior is maintained.
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, converting subqueries in the query into multiple 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, it is recommended to directly use the UNNEST hint, as it provides a general optimization strategy.
Syntax
/*+ UNNEST [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.
Examples
The following query example uses the UNNEST hint to enable subquery unnesting-related query rewriting.
-- Assuming that the t1 table has been created
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
-- The following query example uses the UNNEST hint to trigger 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 the optimizer from rewriting 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 apply the hint to.
Examples
The following query example uses the NO_UNNEST hint to disable subquery unnesting-related query rewriting.
-- Assuming that the t1 table has been created
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
-- The following query example uses the NO_UNNEST hint to ensure that the subquery is not automatically rewritten into a join operation.
SELECT (SELECT /*+no_unnest*/ COUNT(*)
FROM t1 b
WHERE a.c2 = b.c2)
FROM t1 a;