In SQL queries, heuristics-related hints aim to quickly find feasible solutions, even if they are not the optimal ones, especially in complex queries. The database optimizer will attempt 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 other aggregate functions), the AGGR_FIRST_UNNEST hint enables aggregate-first subquery promotion for query rewriting. Its reverse operation is NO_AGGR_FIRST_UNNEST. |
NO_AGGR_FIRST_UNNEST |
Disables aggregate-first subquery promotion for query rewriting. Its reverse operation is AGGR_FIRST_UNNEST. |
COUNT_TO_EXISTS |
Enables query rewriting by converting COUNT subqueries into EXISTS subqueries. Its reverse operation is NO_COUNT_TO_EXISTS. |
NO_COUNT_TO_EXISTS |
Disables query rewriting by converting COUNT subqueries into 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 optimizer from eliminating 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 to expand CTEs. Its reverse operation is INLINE. |
JOIN_FIRST_UNNEST |
When handling subqueries with aggregate functions, attempts to prioritize join operations for query rewriting. Its reverse operation is NO_JOIN_FIRST_UNNEST. |
NO_JOIN_FIRST_UNNEST |
Prevents the use 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) 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) when 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 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 when dealing 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 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 for set operations. Its reverse is SIMPLIFY_SET. |
SIMPLIFY_SUBQUERY |
Enables query rewriting for subqueries. Its reverse is NO_SIMPLIFY_SUBQUERY. |
NO_SIMPLIFY_SUBQUERY |
Disables query rewriting for subqueries. Its reverse is SIMPLIFY_SUBQUERY. |
SIMPLIFY_WINFUNC |
Enables query rewriting for window functions. Its reverse is NO_SIMPLIFY_WINFUNC. |
NO_SIMPLIFY_WINFUNC |
Disables query rewriting for window functions. Its reverse is SIMPLIFY_WINFUNC. |
UNNEST |
Enables subquery unnesting. Its reverse is NO_UNNEST. |
NO_UNNEST |
Disables subquery unnesting. Its reverse is UNNEST. |
AGGR_FIRST_UNNEST Hint
The AGGR_FIRST_UNNEST hint tells the optimizer to prioritize independent aggregation calculations over merging them into the main query when handling subqueries with aggregate functions. Its opposite is the NO_AGGR_FIRST_UNNEST hint.
Direct use of the AGGR_FIRST_UNNEST or NO_AGGR_FIRST_UNNEST hints is not recommended because they directly affect the rewriting behavior of subquery lifting. Generally, if you need more fundamental control over subquery lifting behavior, 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 follows the hint.
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 instructs 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 opposite is the AGGR_FIRST_UNNEST hint.
Direct use of the AGGR_FIRST_UNNEST or NO_AGGR_FIRST_UNNEST hints is not recommended because they directly affect the rewriting behavior of subquery lifting. Generally, if you need more fundamental control over subquery lifting behavior, 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 follows the hint.
Examples
The following query example uses the NO_AGGR_FIRST_UNNEST hint. The query retains the original form of the aggregate subquery and does not perform aggregation-priority 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 opposite is the NO_COUNT_TO_EXISTS hint.
Syntax
/*+ COUNT_TO_EXISTS [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
qb_name_list: Optional. Specifies particular query block subqueries to rewrite. If this parameter is omitted, the hint attempts to rewrite all subqueries.
Examples
The following query example uses the COUNT_TO_EXISTS hint to enable query rewriting for COUNT subqueries.
-- The `COUNT_TO_EXISTS` hint instructs the optimizer to convert embedded `COUNT` subqueries into more efficient `EXISTS` forms.
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 opposite is the COUNT_TO_EXISTS hint.
Syntax
/*+ NO_COUNT_TO_EXISTS [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.qb_name_list: Optional. Specifies particular query block subqueries to disable rewriting for. If this parameter is omitted, the hint attempts to disable rewriting for all subqueries.
Examples
The following query example uses the NO_COUNT_TO_EXISTS hint to prevent query rewriting for COUNT subqueries.
-- The `NO_COUNT_TO_EXISTS` hint instructs the optimizer to retain the original form of the COUNT subquery without 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 rewriting that eliminates joins. Its opposite is the NO_ELIMINATE_JOIN hint.
Syntax
/*+ ELIMINATE_JOIN [ ( [ @ qb_name ] [ table_list ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.table_list: Optional. Specifies particular join tables to eliminate. If this parameter is omitted, the hint attempts to eliminate joins for all tables.
Examples
The following query example uses the ELIMINATE_JOIN hint to enable query rewriting that eliminates joins.
-- The `ELIMINATE_JOIN` hint instructs the optimizer 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 opposite is the ELIMINATE_JOIN hint.
Syntax
/*+ NO_ELIMINATE_JOIN [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to apply the hint to.table_list: Optional. Specifies particular join tables to disable join elimination for. If this parameter is omitted, the hint attempts to disable join elimination for all tables.
Examples
The following query example uses the NO_ELIMINATE_JOIN hint to prevent query rewriting that eliminates joins.
-- The `NO_ELIMINATE_JOIN` hint instructs the optimizer to retain the join operation.
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 that use 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 which the hint applies.
Examples
The following query example uses 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 that use 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 which the hint applies.
Examples
The following query example uses 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 `FAST_MINMAX` hint to disable the optimizer's optimization for MAX(c1).
SELECT /*+ NO_FAST_MINMAX */ MAX(c1) FROM t1;
INLINE Hint
The INLINE hint specifies the expansion of common table expressions (CTEs) 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 which the hint applies.
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 CTEs. The reverse operation is the INLINE hint.
Syntax
/*+ MATERIALIZE [ ( [ @ qb_name ] [ qb_name_list ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which the hint applies.qb_name_list: Optional. Specifies the query blocks for which CTEs should not be materialized. If this parameter is omitted, CTEs are not materialized. It is recommended to use theMATERIALIZEhint only within CTEs defined inwith cte.
Examples
The following query example uses the MATERIALIZE hint to specify that CTEs defined in with cte should not be expanded or materialized.
-- 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 the use of join operations to optimize subqueries containing 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 which the hint applies.
Examples
The following query example uses the JOIN_FIRST_UNNEST hint to enable query rewriting for aggregate subqueries, prioritizing join operations.
-- Use the `JOIN_FIRST_UNNEST` hint to prioritize join operations 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 query rewriting for aggregate subquery lifting, prioritizing join operations. When using the NO_JOIN_FIRST_UNNEST hint, the query optimizer will not consider join operations as a priority strategy. 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 which the hint applies.
Examples
The following query example uses the NO_JOIN_FIRST_UNNEST hint to disable query rewriting for aggregate subqueries, prioritizing join operations.
-- Use the `NO_JOIN_FIRST_UNNEST` hint to prioritize 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. The 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 shows how to use 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. The 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 shows how to use 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 enables query rewriting that converts LEFT OUTER JOIN or RIGHT OUTER JOIN to a more efficient INNER JOIN. The 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 shows how to use the OUTER_TO_INNER hint to enable query rewriting that converts OUTER JOIN to INNER JOIN.
-- The WHERE condition b.c1 > 0 applies only to table b. This condition 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 query rewriting that converts LEFT OUTER JOIN or RIGHT OUTER JOIN to a more efficient INNER JOIN. The 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 shows how to use the NO_OUTER_TO_INNER hint to disable query rewriting that converts LEFT OUTER JOIN or RIGHT OUTER JOIN to a more efficient INNER JOIN.
-- The WHERE condition b.c1 > 0 applies only to table b. This condition 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 query rewriting that derives new predicates from existing ones and pushes them down. This rewriting is typically effective for nested queries or joined queries. The 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 shows how to use the PRED_DEDUCE hint to enable query rewriting 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 query rewriting that derives new predicates from existing ones and pushes them down. The 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 shows how to use the NO_PRED_DEDUCE hint to disable query rewriting 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 rewriting technique, known as Project Pruning, improves query efficiency, particularly when handling inner query blocks. The reverse operation is the NO_PROJECT_PRUNE hint.
Syntax
/*+ PROJECT_PRUNE [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the specific query block to which thePROJECT_PRUNEhint applies, providing finer-grained control over query optimization.
Examples
The following example shows how to use the PROJECT_PRUNE hint to enable the Project Pruning rewriting mechanism. This allows 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 specific performance or functional requirements necessitate retaining these columns, using the NO_PROJECT_PRUNE hint becomes crucial. Its opposite is the PROJECT_PRUNE hint.
Syntax
/*+ NO_PROJECT_PRUNE [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies which query block should avoid applying Project Pruning. This provides more precise control when multiple query blocks exist.
Examples
The following query example uses the NO_PROJECT_PRUNE hint 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 results 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 (uplift) subquery expressions to the parent query level. Its opposite is the NO_PULLUP_EXPR hint.
Syntax
/*+ PULLUP_EXPR [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to apply the hint.
Examples
The following query example uses the PULLUP_EXPR hint to enable expression uplifting rewrite. After the expression uplifting 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 uplifting optimization. Its opposite is the PULLUP_EXPR hint.
Syntax
/*+ NO_PULLUP_EXPR [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to apply the hint.
Examples
The following query example uses the NO_PULLUP_EXPR hint to disable expression uplifting rewrite. After the expression uplifting 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. This can significantly improve the efficiency of paginated queries. Its opposite is the NO_PUSH_LIMIT hint.
Syntax
/*+ PUSH_LIMIT [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to apply the hint.
Examples
The following query examples use the PUSH_LIMIT hint 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 with 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 opposite is the PUSH_LIMIT hint.
Syntax
/*+ NO_PUSH_LIMIT [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to apply the hint.
Examples
The following query example uses the NO_PUSH_LIMIT hint 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 with 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 replaces 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 ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to apply the hint.
Examples
The following query example uses the REPLACE_CONST hint 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 example with the REPLACE_CONST hint, which simplifies 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 rewrites in certain scenarios. Its opposite is the REPLACE_CONST hint.
Syntax
/*+ NO_REPLACE_CONST [ ( [ @ qb_name ] ) ] */
Parameter description
@qb_name: Optional. Specifies the query block to apply the hint.
Examples
The following query example uses the NO_REPLACE_CONST hint 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 example with the NO_REPLACE_CONST hint, which maintains 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 simplify queries involving the DISTINCT operation. Its opposite is the NO_SIMPLIFY_DISTINCT hint.
Syntax
/*+ SIMPLIFY_DISTINCT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this 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 DISTINCT queries. This can be useful in specific scenarios where you want to preserve the original query structure and prevent unexpected rewrites. Its opposite 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
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 opposite is the NO_SIMPLIFY_EXPR hint.
Syntax
/*+ SIMPLIFY_EXPR [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this 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 performance 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 opposite 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
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 disable 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 is used to optimize queries containing the GROUP BY clause. This hint enables the optimizer to rewrite and simplify GROUP BY operations. Its opposite is the NO_SIMPLIFY_GROUP_BY hint.
Syntax
/*+ SIMPLIFY_GROUP_BY [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this 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 optimize the execution of aggregate operations by rewriting the query.
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 the GROUP BY clause, preserving the original query logic. Its opposite 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 applies.
Examples
The following query example shows how to use the NO_SIMPLIFY_GROUP_BY hint to preserve 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 output for LIMIT clauses, improving the performance of paginated queries. Its opposite is the NO_SIMPLIFY_LIMIT hint.
Syntax
/*+ SIMPLIFY_LIMIT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this 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 `NO_SIMPLIFY_LIMIT` in the example to correct the hint usage.
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 for LIMIT clauses. Its opposite is the SIMPLIFY_LIMIT hint.
Syntax
/*+ NO_SIMPLIFY_LIMIT [ ( [ @ qb_name ] ) ] */
Parameters
@qb_name: Optional. Specifies the query block to which this 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 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 ORDER BY operations.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- `SIMPLIFY_ORDER_BY` Hint enables the optimizer to rewrite and optimize the execution of sorting operations.
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 prevents any query rewriting related to ORDER BY operations, ensuring the original logic of the query remains unchanged. 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 maintain 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
SIMPLIFY_SUBQUERY Hint enables the 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 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, maintaining 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 prevent 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 the query block of a set query. Therefore, you must specify the query block to which the hint applies by 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, the hint /*+simplify_set(@set$1)*/ 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
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 the query block of a set query. Therefore, you must specify the query block to which the hint applies by using the QB name method.
Examples
The following query example uses the NO_SIMPLIFY_SET hint to prevent 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, the hint /*+no_simplify_set(@set$1)*/ 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. 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.
-- Assume 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 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 apply the hint to.
Examples
The following query example uses the NO_SIMPLIFY_WINFUNC hint to disable window function-related query rewriting.
-- Assume that the t1 table has been created.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The following query example applies the NO_SIMPLIFY_WINFUNC hint to maintain 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, 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, we recommend using the UNNEST hint directly, 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.
-- Assume 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 subquery unnesting-related query rewriting, even when a subquery can be effectively rewritten as 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.
-- Assume that the t1 table has been created.
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
-- The following query example applies the NO_UNNEST hint to prevent 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;