Transform hints: Heuristics transform hint

The hints listed in this topic are used in SQL queries to quickly find a feasible solution, which may not be the optimal one, especially for complex queries. In such cases, the database optimizer tries to apply various rules and transformation strategies to find the most effective execution plan.

AGGR_FIRST_UNNEST hint

The AGGR_FIRST_UNNEST hint directs the optimizer to prioritize independent aggregate computations over merging with the main query when processing aggregate function subqueries. The NO_AGGR_FIRST_UNNEST hint is the reverse operation of this hint.

It is recommended not to use the AGGR_FIRST_UNNEST/NO_AGGR_FIRST_UNNEST hints directly because they directly affect the rewrite behavior of subquery lifting. If you need more fundamental control over the lifting behavior of subqueries, it is recommended to use the UNNEST or NO_UNNEST hint instead.

Syntax

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

Parameters

• @qb_name: optional, specifies the query block to which the hint applies.

Examples

In the following query, the AGGR_FIRST_UNNEST hint is used to enable aggregate computations for aggregate function subqueries and prioritize subquery lifting during query rewriting.

-- The hint directs the optimizer to prioritize aggregate computations for subqueries containing the AVG aggregate function 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 aggregate computations when processing aggregate function subqueries. The reverse operation of this hint is the AGGR_FIRST_UNNEST hint.

It is recommended not to use the AGGR_FIRST_UNNEST/NO_AGGR_FIRST_UNNEST hints directly because they directly affect the rewrite behavior of subquery lifting. If you need more fundamental control over the lifting behavior of subqueries, it is recommended to use the UNNEST or NO_UNNEST hint instead.

Syntax

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

Parameters

• @qb_name: optional, specifies the query block to which the hint applies.

Examples

In the following query, the NO_AGGR_FIRST_UNNEST hint is used to maintain the original form of aggregate subqueries without prioritizing 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 the rewriting of COUNT subqueries into EXISTS subqueries. The reverse operation of this hint is the NO_COUNT_TO_EXISTS hint.

Syntax

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

Parameters

• qb_name_list: can be specified to rewrite specific query block subqueries. If the parameter is not specified, the hint specifies to attempt to rewrite all subqueries.

Examples

In the following query, the COUNT_TO_EXISTS hint is used to enable the rewriting of the COUNT subquery.

-- The `COUNT_TO_EXISTS` hint directs the optimizer to convert the embedded `COUNT` subquery into a more efficient `EXISTS` form.
SELECT /*+ COUNT_TO_EXISTS */ * FROM t1
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.c1 = t1.c2) > 0;

NO_COUNT_TO_EXISTS hint

The COUNT_TO_EXISTS hint prevents the query rewrite that converts a COUNT subquery into an EXISTS subquery. The NO_COUNT_TO_EXISTS hint has the opposite effect.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.
• qb_name_list: the names of query blocks whose subqueries are to be prevented from being rewritten. If this parameter is not specified, the hint prevents all subqueries from being rewritten.

Examples

The following example shows how to use the NO_COUNT_TO_EXISTS hint to prevent the conversion of a COUNT subquery.

-- Use the `NO_COUNT_TO_EXISTS` hint to keep the 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 for join elimination. The NO_ELIMINATE_JOIN hint has the opposite effect.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.
• table_list: the list of tables involved in joins to which the join elimination is applied. If this parameter is not specified, the hint specifies that join elimination be attempted for all join tables.

Examples

The following example shows how to use the ELIMINATE_JOIN hint to enable query rewriting for join elimination.

-- Use the `ELIMINATE_JOIN` hint to find and eliminate self joins 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 prevents query rewriting for join elimination and retains all specified joins. The ELIMINATE_JOIN hint has the opposite effect.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.
• table_list: the list of tables involved in joins to which join elimination is prevented. If this parameter is not specified, the hint specifies that join elimination be prevented for all join tables.

Examples

The following example shows how to use the NO_ELIMINATE_JOIN hint to prevent query rewriting for join elimination.

-- Use the `NO_ELIMINATE_JOIN` hint to ensure 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 the optimization of MIN/MAX aggregate functions. It prompts the database query optimizer to rewrite the query to accelerate the retrieval of minimum and maximum values. The NO_FAST_MINMAX hint has the opposite effect.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

The following example shows how to use the FAST_MINMAX hint to enable query rewriting for the optimization of MIN/MAX aggregate functions.

CREATE TABLE t1(c1 int primary key, c2 int, c3 int);
-- Use the `FAST_MINMAX` hint to optimize the calculation of MAX(c1).
SELECT /*+ FAST_MINMAX */ MAX(c1) FROM t1;

NO_FAST_MINMAX hint

The NO_FAST_MINMAX hint prevents the query optimizer from rewriting queries that contain MIN/MAX aggregate functions. The FAST_MINMAX hint is the opposite of the NO_FAST_MINMAX hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

The following example shows how to use the NO_FAST_MINMAX hint to prevent the optimizer from optimizing the MIN/MAX operations in the query.

CREATE TABLE t1(c1 int primary key, c2 int, c3 int);
-- Use the `FAST_MINMAX` hint to prevent the optimizer from accelerating the MAX(c1) operation.
SELECT /*+ NO_FAST_MINMAX */ MAX(c1) FROM t1;

INLINE hint

The INLINE hint specifies that a common table expression (CTE) be expanded, and integrated into the main query. The MATERIALIZE hint is the opposite of the INLINE hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

The following example shows how to use the INLINE hint to expand the CTE defined by the with cte clause.

-- Use the `INLINE` hint to directly use the subquery defined in the with cte clause 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 specifies whether to extract a CTE or prevent the expansion of a CTE. The INLINE hint is the opposite of the MATERIALIZE hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.
• qb_name_list: the names of the query blocks from which CTEs are not extracted. If this parameter is not specified, CTEs are not extracted. We recommend that you specify this parameter only for a CTE defined by the with cte clause.

Examples

The following examples show how to use the MATERIALIZE hint to prevent the expansion of the CTE defined by the with cte clause, or to extract CTEs from specific query blocks in the query.

-- Example 1: materialize a single CTE
WITH cte AS (
  SELECT /*+MATERIALIZE*/ * FROM t1
)
SELECT * FROM cte;

-- Example 2: materialize multiple query blocks
SELECT /*+MATERIALIZE(("SEL$2", "SEL$3"))*/ v1.*
FROM   (SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1) v1,
       (SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1) v2,
       (SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1) v3,
       (SELECT t1.* FROM t1 JOIN t2 ON t1.c1 = t2.c1) v4;

JOIN_FIRST_UNNEST hint

The JOIN_FIRST_UNNEST hint enables query rewriting for aggregate subqueries, specifically considering join operations to optimize subqueries containing aggregate functions. The NO_JOIN_FIRST_UNNEST hint is the reverse operation of this hint.

It is recommended not to directly use the JOIN_FIRST_UNNEST or NO_JOIN_FIRST_UNNEST hint to intervene in subquery unnesting rewriting. Instead, use the UNNEST or NO_UNNEST hint for such purposes.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

The following example shows how to use the JOIN_FIRST_UNNEST hint to enable query rewriting for aggregate subqueries, prioritizing join operations in the execution plan for subqueries containing aggregate functions.

-- Use the /*+ JOIN_FIRST_UNNEST */ hint to prioritize join operations in the execution plan when processing subqueries containing aggregate functions (such as AVG).
SELECT * FROM t1
WHERE t1.c1 > (
  SELECT /*+ JOIN_FIRST_UNNEST */ AVG(d1)
  FROM t2
  WHERE t1.c2 = t2.d2
);

NO_JOIN_FIRST_UNNEST hint

The NO_JOIN_FIRST_UNNEST hint is used to disable query rewriting for aggregate subqueries, preventing the query optimizer from considering join prioritization strategies. The JOIN_FIRST_UNNEST hint is the reverse operation of this hint.

It is recommended not to directly use the JOIN_FIRST_UNNEST or NO_JOIN_FIRST_UNNEST hint to intervene in subquery unnesting rewriting. Instead, use the UNNEST or NO_UNNEST hint for such purposes.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

The following example shows how to use the NO_JOIN_FIRST_UNNEST hint to disable query rewriting for aggregate subqueries, preventing join prioritization in subquery unnesting.

-- Use the /*+ NO_JOIN_FIRST_UNNEST */ hint to prevent join prioritization in subquery unnesting.
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 NO_LEFT_TO_ANTI hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.
• table_list: the name list of the right tables of the LEFT JOIN to which the query rewriting is applied. This parameter is optional. If not specified, the hint attempts to apply query rewriting to all OUTER JOIN operations.

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 LEFT_TO_ANTI hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following query, the NO_LEFT_TO_ANTI hint is used to prohibit 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, namely, converting LEFT OUTER JOIN or RIGHT OUTER JOIN to INNER JOIN, which are more efficient. The NO_OUTER_TO_INNER hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following query, the OUTER_TO_INNER hint is used to enable query rewriting that converts outer joins to inner joins.

-- The filter condition b.c1 > 0 applies only to b. It implicitly eliminates the possibility of null values in the right table of the outer join, thereby converting the outer join into 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 will not convert outer joins to inner joins. The OUTER_TO_INNER hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following query, the NO_OUTER_TO_INNER hint is used to prohibit query rewriting that converts outer joins to inner joins.

-- The condition in the `WHERE` clause may implicitly indicate an inner join. The `NO_OUTER_TO_INNER` hint ensures that the outer join logic remains unchanged.
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 uses existing predicates to derive new predicates and push down predicates. This rewriting is usually applicable to nested queries or join queries. The NO_PRED_DEDUCE hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following query, the PRED_DEDUCE hint is used to enable predicate deduction for the outer query block.

-- Enable predicate deduction for the outer query.
select /*+pred_deduce*/ c1
from (
  -- Disable subquery merging to ensure that the no_merge hint takes effect.
  select /*+no_merge*/ t1.* from t1
)
where c1 = 3;

NO_PRED_DEDUCE hint

The NO_PRED_DEDUCE hint prevents the optimizer from generating new predicates based on existing ones. The PRED_DEDUCE hint is the opposite of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional. It provides finer-grained control over query optimization.

Examples

The following example shows how to use the NO_PRED_DEDUCE hint to prevent the outer query block from participating in predicate deduction-based query rewriting.

-- Disable predicate deduction for the outer query.
select /*+no_pred_deduce*/ c1
from (
  -- Disable subquery merging to ensure that the no_merge hint takes effect.
  select /*+no_merge*/ t1.* from t1
)
where c1 = 3;

PROJECT_PRUNE hint

The PROJECT_PRUNE hint optimizes query execution by effectively reducing the amount of data processed, especially in large datasets with complex queries. It prunes unnecessary columns to reduce the output of a query block (QB). This query rewriting technique, known as project pruning, helps improve query efficiency, particularly for inner query blocks. The NO_PROJECT_PRUNE hint is the opposite of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional. It provides finer-grained control over query optimization.

Examples

The following example shows how to use the PROJECT_PRUNE hint to enable project pruning.

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

NO_PROJECT_PRUNE hint

The NO_PROJECT_PRUNE hint disables project pruning, a query rewriting technique that automatically prunes unused output columns during query optimization. Use this hint when you need to retain these columns for specific performance or feature requirements. The PROJECT_PRUNE hint is the opposite of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied, to avoid project pruning. This parameter is optional. It provides more precise control when multiple query blocks are involved.

Examples

The following example shows how to use the NO_PROJECT_PRUNE hint to disable project pruning.

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. The NO_PULLUP_EXPR hint is the opposite of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

The following example shows how to use the PULLUP_EXPR hint to enable expression pulling up.

-- Assume that t1 is an existing table with the following structure:
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);

-- Execute the following query with the PULLUP_EXPR hint:
SELECT *
FROM (SELECT /*+no_merge pullup_expr*/ t1.*,
             (SELECT c2 FROM t1 WHERE c1 = 1)
      FROM t1)
WHERE c1 > 1000;

NO_PULLUP_EXPR hint

The NO_PULLUP_EXPR hint disables the query optimizer to perform expression pulling-up optimization. The PULLUP_EXPR hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: optional, specifies the query block to which the hint is applied.

Examples

In the following example, the NO_PULLUP_EXPR hint is used to prevent expression pulling-up rewrite. After the expression pulling-up rewrite is triggered, the inner view's select subquery is blocked from being pulled up to the outer layer.

-- Assume that t1 has the existing schema.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);

-- Execute the 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 (or apply) the LIMIT clause (which limits the number of returned rows) to the inner side of a join query, such as an outer join or a Cartesian product. This can significantly improve the efficiency of paging queries. The NO_PUSH_LIMIT hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: optional, specifies the query block to which the hint is applied.

Examples

In the following query, the PUSH_LIMIT hint is used to enable limit pushing join rewrite.

-- Assume that t1 has the existing schema.
CREATE TABLE t1(c1 int primary key, c2 int, c3 int);

-- Query 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 changing the original query structure, especially when you do not want the LIMIT clause to be pushed down into the join query. The PUSH_LIMIT hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: optional, specifies the query block to which the hint is applied.

Examples

In the following query, the NO_PUSH_LIMIT hint is used to disable limit pushing join rewrite.

-- Assume that t1 has the existing schema.
CREATE TABLE t1(c1 int primary key, c2 int, c3 int);

-- Query 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 rewriting, which replaces equivalent conditions in the query with constants to simplify the query and improve its efficiency. The NO_REPLACE_CONST hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: optional, specifies the query block to which the hint is applied.

Examples

In the following query, the REPLACE_CONST hint is used to enable constant propagation query rewriting.

-- Assume that t1 has the existing schema.
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);

-- Query 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 prevents constant propagation query rewriting to avoid unnecessary rewriting in certain scenarios. The REPLACE_CONST hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following query, the NO_REPLACE_CONST hint is used to prevent constant propagation query rewriting.

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

-- A query that uses the NO_REPLACE_CONST hint to retain 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 distinct queries for simplification and optimization. The NO_SIMPLIFY_DISTINCT hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following query, the SIMPLIFY_DISTINCT hint is used to enable a series of distinct query rewriting.

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 prevents the simplification of distinct queries. This can help maintain the original query in specific scenarios and prevent the optimizer from automatically making undesired changes. The SIMPLIFY_DISTINCT hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following query, the NO_SIMPLIFY_DISTINCT hint is used to prevent a series of distinct query rewriting.

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. The NO_SIMPLIFY_EXPR hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following query, the SIMPLIFY_EXPR hint is used to enable the optimizer to convert and simplify expressions in the query.

-- Use the `SIMPLIFY_EXPR` hint to optimize query efficiency by removing redundant logic during condition simplification.
SELECT /*+simplify_expr*/ 1 FROM t1 WHERE c1 = 1 OR 1 = 1;

NO_SIMPLIFY_EXPR hint

The NO_SIMPLIFY_EXPR hint disables the optimizer from converting and simplifying expressions in queries. The SIMPLIFY_EXPR hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following query, the NO_SIMPLIFY_EXPR hint is used to disable the conversion and simplification of expressions in the query.

-- Use the `NO_SIMPLIFY_EXPR` hint to disable 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 that contain a GROUP BY clause. This hint enables the query optimizer to simplify and rewrite GROUP BY operations. The NO_SIMPLIFY_GROUP_BY hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following example, the SIMPLIFY_GROUP_BY hint is used to enable query rewriting related to the Group By operation.

CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The `SIMPLIFY_GROUP_BY` hint enables the optimizer to optimize the execution of aggregate operations through rewriting.
SELECT /*+simplify_group_by*/ COUNT(DISTINCT c1) FROM t1 GROUP BY c2;

NO_SIMPLIFY_GROUP_BY hint

The NO_SIMPLIFY_GROUP_BY hint prevents any query rewriting related to GROUP BY operations to maintain the original logic of the query. The SIMPLIFY_GROUP_BY hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following example, the NO_SIMPLIFY_GROUP_BY hint is used to maintain the original GROUP BY logic and prevent any automatic query optimization.

CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);

SELECT /*+no_simplify_group_by*/ COUNT(DISTINCT c1) FROM t1 GROUP BY c2;

SIMPLIFY_LIMIT hint

The SIMPLIFY_LIMIT hint enables the optimizer to simplify query output for the LIMIT clause, improving the performance of paging queries. The NO_SIMPLIFY_LIMIT hint is the reverse operation of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following example, the SIMPLIFY_LIMIT hint is used to enable rewriting related to the Limit operation.

CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);

-- Use `SIMPLIFY_LIMIT` instead of `NO_SIMPLIFY_LIMIT` in the following hint usage 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 query rewriting that involves the LIMIT clause. The SIMPLIFY_LIMIT hint is the reverse of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following example, the NO_SIMPLIFY_LIMIT hint is used to prevent query rewriting that involves the LIMIT clause.

CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);

-- Use the `NO_SIMPLIFY_LIMIT` hint to ensure that the original pagination logic is retained.
SELECT /*+no_simplify_limit*/ *
FROM (SELECT /*+no_rewrite*/ ROWNUM cnt,
             (SELECT c2 FROM t1 WHERE c1 = 1)
      FROM t1)
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

SIMPLIFY_ORDER_BY hint

The SIMPLIFY_ORDER_BY hint is used to optimize queries that contain the ORDER BY clause. When this hint is specified, the query optimizer simplifies and rewrites the ORDER BY operation. The NO_SIMPLIFY_ORDER_BY hint is the reverse of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following example, the SIMPLIFY_ORDER_BY hint is used to enable query rewriting that involves the Group By operation.

CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
-- The `SIMPLIFY_ORDER_BY` hint enables the optimizer to optimize the execution of the sorting operation by rewriting the query.
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 that involves the ORDER BY clause to retain the original query logic. The SIMPLIFY_ORDER_BY hint is the reverse of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following example, the NO_SIMPLIFY_ORDER_BY hint is used to retain 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 rewriting of nested subqueries to simplify query logic and eliminate unnecessary subquery structures. The NO_SIMPLIFY_SUBQUERY hint is the reverse of this hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

In the following example, the SIMPLIFY_SUBQUERY hint is used to enable query rewriting that involves subqueries.

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

-- The following example shows how to use the `SIMPLIFY_SUBQUERY` hint to enable the optimizer to simplify or eliminate unnecessary subqueries.
SELECT /*+simplify_subquery*/ * FROM t1
WHERE EXISTS (SELECT * FROM t1);

NO_SIMPLIFY_SUBQUERY hint

The NO_SIMPLIFY_SUBQUERY hint prevents the optimizer from automatically rewriting subqueries, thus preserving the original query structure and logic. Its counterpart is the SIMPLIFY_SUBQUERY hint.

Syntax

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

Parameters

• @qb_name: Optional. The query block to which the hint is applied.

Examples

In the following example, the NO_SIMPLIFY_SUBQUERY hint is used to prevent the optimizer from rewriting the subquery.

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

-- The following example shows how to use the `NO_SIMPLIFY_SUBQUERY` hint to ensure that the subquery remains unchanged and is not optimized.
SELECT /*+no_simplify_subquery*/ * FROM t1
WHERE EXISTS (SELECT * FROM t1);

SIMPLIFY_SET hint

The SIMPLIFY_SET hint enables the optimizer to rewrite set operations such as UNION, INTERSECT, and EXCEPT. Its counterpart is the NO_SIMPLIFY_SET hint.

Syntax

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

Parameters

• @qb_name: Optional. The query block to which the hint is applied.

Examples

In the following example, the SIMPLIFY_SET hint is used to enable the optimizer to rewrite the set operation in the query.

-- Assume that the t1 table exists.
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;

The preceding example contains the hint /*+simplify_set(@set$1)*/, which indicates that the database optimizer is disabled for the specific set operation (UNION ALL). In this case, @set$1 is a query block name (QB name) that identifies the entire UNION ALL statement.

NO_SIMPLIFY_SET hint

The NO_SIMPLIFY_SET hint disables query rewriting for set-related queries. The SIMPLIFY_SET hint is its inverse operation.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

The following example shows how to use the NO_SIMPLIFY_SET hint to disable query rewriting for set-related queries.

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

-- Use the NO_SIMPLIFY_SET hint to disable query rewriting for set-related queries.
SELECT /*+no_simplify_set(@set$1)*/ * FROM t1
UNION ALL
SELECT * FROM t1 WHERE 1 = 0;

The preceding example uses the /*+no_simplify_set(@set$1)*/ hint to disable automatic simplification of the UNION ALL query by the database optimizer. In this case, @set$1 is a Query Block Name (QB Name) that identifies the entire UNION ALL query statement.

SIMPLIFY_WINFUNC hint

The SIMPLIFY_WINFUNC hint enables the optimizer to effectively rewrite queries that contain window functions. The NO_SIMPLIFY_WINFUNC hint is its inverse operation.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

The following example shows how to use the SIMPLIFY_WINFUNC hint.

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

-- Use 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 that contain window functions. Use this hint when you want to ensure that specific logic remains unchanged. The SIMPLIFY_WINFUNC hint is its inverse operation.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

The following example shows how to use the NO_SIMPLIFY_WINFUNC hint.

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

-- The query statement that contains the NO_SIMPLIFY_WINFUNC hint retains 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 subqueries into joins to improve query performance. It converts subqueries in queries into multiple joins. The NO_UNNEST hint is the opposite of the UNNEST hint.

OceanBase Database supports further subquery elevation optimization, such as the AGGR_FIRST_UNNEST hint and the JOIN_FIRST_UNNEST hint. The AGGR_FIRST_UNNEST hint and the JOIN_FIRST_UNNEST hint direct the optimizer to prioritize aggregation or join operations respectively during subquery elevation. In most cases, we recommend that you directly use the UNNEST hint, because it provides a general optimization strategy.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

The following example shows how to use the UNNEST hint to enable subquery elevation-related query rewriting.

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

-- Execute the following statement to enable subquery elevation:
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 such a rewrite can be performed. The UNNEST hint is the opposite of the NO_UNNEST hint.

Syntax

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

Parameters

• @qb_name: the name of the query block to which the hint is applied. This parameter is optional.

Examples

The following example shows how to use the NO_UNNEST hint to disable subquery elevation-related query rewriting.

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

-- Execute the following statement to disable subquery elevation:
SELECT (SELECT /*+no_unnest*/ COUNT(*)
        FROM t1 b
        WHERE a.c2 = b.c2)
FROM t1 a;