Heuristics transform hints

In SQL queries, heuristics-related hints aim to quickly find feasible solutions, even if they are not optimal, especially in complex queries. The database optimizer will attempt to apply various rules and transformation strategies to find the most effective execution plan.

AGGR_FIRST_UNNEST Hint

The AGGR_FIRST_UNNEST Hint enables the optimizer to 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 Hint because they directly affect the rewriting behavior of subquery lifting. In most cases, if you need more basic control over the subquery lifting behavior, it is recommended to use the UNNEST or NO_UNNEST Hint instead.

Syntax

/*+ AGGR_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */

Parameter description

• @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 disables 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 Hint because they directly affect the rewriting behavior of subquery lifting. In most cases, if you need more basic control over the subquery lifting behavior, it is recommended to use the UNNEST or NO_UNNEST Hint instead.

Syntax

/*+ NO_AGGR_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */

Parameter description

• @qb_name: Optional. Specifies the query block to which the hint applies.

Examples

The following query example uses the NO_AGGR_FIRST_UNNEST Hint. This query retains the original form of the aggregate subquery and does not perform aggregation-prioritized 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 ] ) ] */

Parameter description

• qb_name_list: Specifies which query block subqueries to rewrite. If not specified, the hint applies to 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 directs the optimizer to convert nested `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 reverse operation is the COUNT_TO_EXISTS Hint.

Syntax

/*+ NO_COUNT_TO_EXISTS [ ( [ @ qb_name ] ) ] */

Parameter description

• @qb_name: Optional. Specifies the query block to which the hint applies.
• qb_name_list: Specifies which query block subqueries to disable for rewriting. If not specified, the hint applies to all subqueries.

Examples

The following query example uses the NO_COUNT_TO_EXISTS Hint to disable query rewriting for COUNT subqueries.

-- The `NO_COUNT_TO_EXISTS` Hint ensures that the COUNT subquery is used as-is 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 reverse operation is the NO_ELIMINATE_JOIN Hint.

Syntax

/*+ ELIMINATE_JOIN [ ( [ @ qb_name ] [ table_list ] ) ] */

Parameter description

• @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

The following query example uses the ELIMINATE_JOIN Hint to enable query rewriting that eliminates joins.

-- The `ELIMINATE_JOIN` Hint directs 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 reverse operation is the ELIMINATE_JOIN Hint.

Syntax

/*+ NO_ELIMINATE_JOIN [ ( [ @ qb_name ] ) ] */

Parameter description

• @qb_name: Optional. Specifies the query block to which the hint applies.
• table_list: Optional. Specifies which join tables to disable for elimination. If not specified, 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 join operations are retained.
SELECT /*+ NO_ELIMINATE_JOIN */ *
FROM t1 a, t1 b
WHERE a.c1 = b.c1;

FAST_MINMAX Hint

The FAST_MINMAX hint enables query rewriting for queries using the MIN 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 performed by the NO_FAST_MINMAX hint.

Syntax

/*+ FAST_MINMAX [ ( [ @ qb_name ] ) ] */

Parameters

• @qb_name: Optional. Specifies the query block to which this hint applies.

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 compute 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 performed by the FAST_MINMAX hint.

Syntax

/*+ NO_FAST_MINMAX [ ( [ @ qb_name ] ) ] */

Parameters

• @qb_name: Optional. Specifies the query block to which this hint applies.

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 common table expressions (CTEs), integrating them directly into the main query. The reverse operation is performed by the MATERIALIZE hint.

Syntax

/*+ INLINE [ ( [ @ qb_name ] ) ] */

Parameters

• @qb_name: Optional. Specifies the query block to which this hint applies.

Examples

The following query example uses 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 common table expressions (CTEs). The reverse operation is performed by the INLINE hint.

Syntax

/*+ MATERIALIZE [ ( [ @ qb_name ] [ qb_name_list ] ) ] */

Parameters

• @qb_name: Optional. Specifies the query block to which this hint applies.
• qb_name_list: Optional. Disables CTE materialization for specific query blocks. If this parameter is omitted, CTE materialization is not performed. It is recommended to use the MATERIALIZE hint only within CTEs defined in with cte.

Examples

The following query example uses the MATERIALIZE hint to disable CTE expansion for the CTE defined in with cte.

-- 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 performed by 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 control subquery lifting.

Syntax

/*+ JOIN_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */

Parameters

• @qb_name: Optional. Specifies the query block to which this hint applies.

Examples

The following query example uses the JOIN_FIRST_UNNEST hint to prioritize join operations when handling subqueries with aggregate functions like AVG.

-- Use the `JOIN_FIRST_UNNEST` hint to prioritize join operations when handling subqueries with aggregate functions like 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, preventing the query optimizer from considering join operations. The reverse operation is performed by 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 control subquery lifting.

Syntax

/*+ NO_JOIN_FIRST_UNNEST [ ( [ @ qb_name ] ) ] */

Parameters

• @qb_name: Optional. Specifies the query block to which this hint applies.

Examples

The following query example uses the NO_JOIN_FIRST_UNNEST hint to disable query rewriting for aggregate subquery lifting.

-- Use the `NO_JOIN_FIRST_UNNEST` hint to prevent the optimizer from considering join operations.
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 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 specific LEFT JOIN to be rewritten. If this parameter is omitted, the hint applies to all OUTER JOIN operations.

Examples

The following query example uses LEFT_TO_ANTI 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 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 query example uses NO_LEFT_TO_ANTI 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 outer joins to inner joins, such as converting LEFT OUTER JOIN or RIGHT OUTER JOIN to a more efficient INNER JOIN. The reverse operation is 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 query example uses OUTER_TO_INNER to enable query rewriting that converts outer joins to inner joins.

-- The WHERE condition b.c1 > 0 applies only to table b, implicitly excluding the possibility of NULL values in the right table of the outer join. This makes 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 outer joins to inner joins. The query optimizer does not convert outer joins to inner joins. The reverse operation is 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 query example uses NO_OUTER_TO_INNER to disable query rewriting that converts outer joins to inner joins.

-- The WHERE condition b.c1 > 0 applies only to table b, implicitly excluding the possibility of NULL values in the right table of the outer join. This makes 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 controls query rewriting that derives new predicates from existing ones and pushes them down. This rewriting is effective for nested queries or joined queries. The reverse operation is 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 query example uses PRED_DEDUCE to enable query rewriting that allows the outer query block to participate in predicate derivation.

-- Enable predicate derivation 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 optimizer from generating new predicates based on existing ones. The reverse operation is 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 query example uses NO_PRED_DEDUCE to disable query rewriting that allows the outer query block to participate in predicate derivation.

-- Disable predicate derivation 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 from query blocks (Query Blocks), improving query efficiency, particularly in handling inner query blocks. The reverse operation is NO_PROJECT_PRUNE hint.

Syntax

/*+ PROJECT_PRUNE [ ( [ @ qb_name ] ) ] */

Parameters

• @qb_name: Optional. Specifies the specific query block to which the PROJECT_PRUNE hint should apply, providing finer-grained control.

Examples

The following query example uses PROJECT_PRUNE 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, 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 inline view's results.

select c1 from (select /*+no_merge no_project_prune*/ t1.* from t1);

PULLUP_EXPR Hint

The PULLUP_EXPR hint enables the optimizer to lift (pull up) 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 lifting rewrite. After the expression lifting query rewrite is triggered, the subquery in the SELECT clause of the inline view is lifted to the outer level.

-- Assume 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 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 lifting rewrite. After the expression lifting query rewrite is triggered, the subquery in the SELECT clause of the inline view is prevented from being lifted to the outer level.

-- Assume 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 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 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 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 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 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 t1 is an existing table
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 avoid unnecessary rewrites 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 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 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 which the hint applies.

Examples

The following query examples demonstrate how to use 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 preserve the original query structure and prevent 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 examples demonstrate how to use 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 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 demonstrates 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 demonstrates 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 optimize queries involving the GROUP BY clause. 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 demonstrates how to use 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 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 GROUP BY-related query rewrites to preserve 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 demonstrates 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 outputs involving the LIMIT clause, thereby improving the performance of paginated 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 demonstrates how to use 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 the `NO_SIMPLIFY_LIMIT` hint to correct the use of the hint 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 prevents the optimizer from rewriting queries 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 which the hint applies.

Examples

The following query example demonstrates how to use 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

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 operation. 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 apply the hint to.

Examples

The following query example uses the SIMPLIFY_ORDER_BY hint to enable query rewriting for ORDER BY-related queries.

CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- `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 ORDER BY 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 apply the hint to.

Examples

The following query example uses 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

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 apply the hint to.

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 apply the hint to.

Examples

The following query example uses the NO_SIMPLIFY_SUBQUERY hint to disable subquery-related query rewriting.

-- Assume that t1 is a pre-created table.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);

-- The query example demonstrates the use of the `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 apply the hint to.

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)*/ is used to disable the automatic simplification of this specific set operation (UNION ALL) query by the database optimizer. @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 apply the hint to.

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, the hint /*+no_simplify_set(@set$1)*/ is used to disable the automatic simplification of this specific set operation (UNION ALL) query by the database optimizer. @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 opposite is the NO_SIMPLIFY_WINFUNC hint.

Syntax

/*+ SIMPLIFY_WINFUNC [ ( [ @ qb_name ] ) ] */

Parameters

• @qb_name: Optional. Specifies the query block to which the hint applies.

Examples

The following query example uses the SIMPLIFY_WINFUNC hint to enable query rewriting for window functions.

-- Assume that the t1 table has been created.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);

-- The following query 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 opposite is the SIMPLIFY_WINFUNC hint.

Syntax

/*+ NO_SIMPLIFY_WINFUNC [ ( [ @ qb_name ] ) ] */

Parameters

• @qb_name: Optional. Specifies the query block to which the hint applies.

Examples

The following query example uses the NO_SIMPLIFY_WINFUNC hint to disable query rewriting for window functions.

-- Assume that the t1 table has been created.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);

-- The following query uses the NO_SIMPLIFY_WINFUNC hint to prevent the optimizer from rewriting 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 opposite 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 which the hint applies.

Examples

The following query example uses the UNNEST hint to enable query rewriting for subquery unnesting.

-- Assume that the t1 table has been created.
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);

-- The following query 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 queries by unnesting subqueries into joins, even when a subquery can be effectively rewritten as a join. Its opposite is the UNNEST hint.

Syntax

/*+ NO_UNNEST [ ( [ @ qb_name ] ) ] */

Parameters

• @qb_name: Optional. Specifies the query block to which the hint applies.

Examples

The following query example uses the NO_UNNEST hint to disable query rewriting for subquery unnesting.

-- Assume that the t1 table has been created.
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);

-- The following query uses the NO_UNNEST hint to prevent the optimizer from rewriting the subquery into a join operation.
SELECT (SELECT /*+no_unnest*/ COUNT(*)
        FROM t1 b
        WHERE a.c2 = b.c2)
FROM t1 a;