Overview of hints

A hint is an SQL comment that you can add to a statement to pass instructions to the optimizer of OceanBase Database. By using hints, you can make the optimizer generate a specific execution plan.

Generally, the optimizer selects the best execution plan for your query without the need for hints. However, if the optimizer's generated plan does not meet your requirements, you can use hints to specify and generate a special execution plan.

Note that after you collect statistics for the relevant tables and evaluate the optimizer's plan using the EXPLAIN PLAN statement without any hints, you should carefully consider whether to use hints. Changes to database conditions or enhancements in future versions may significantly impact the performance of hints in your code.

How to use hints

Considerations and notes

A statement can contain only one hint comment, and this comment must follow the CREATE, SELECT, UPDATE, INSERT, REPLACE, or DELETE keyword. Hints only affect the logic of the execution plan generated by the optimizer, without changing the semantics of the SQL statement.

• The syntax for a hint comment is as follows:

{CREATE|DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ hint_text [,hint_text...] */

• Some rules to keep in mind when defining hints:

A hint, from a syntactic perspective, is a special SQL comment that starts with /*+ and does not allow any spaces. If the server cannot recognize the hint in the SQL statement, the optimizer will ignore the specified hint and use the default plan's execution logic.

• After each SELECT keyword, only one hint can be added, and this hint can contain multiple hints. There can be a space between the + and the hint text, or it can be omitted. If multiple hints are included in the comment, they must be separated by at least one space.

  In the following example, /*+ hint1 */ and /*+ hint3 hint4 */ are valid hints, while /*+ hint2 */ is not.

  select /*+ hint1 */ /*+ hint2 */ * from t1, (select /*+ hint3 hint4 */ t2.* from t2, t3) v where t1.c1 = v.c1;
  

• For queries with multiple possible hint locations, multiple hints can be used.

• Hints with spelling or syntax errors are ignored. However, the database will consider other correctly specified hints within the same comment.

• Hints that do not follow the CREATE, DELETE, INSERT, REPLACE, SELECT, or UPDATE keywords are invalid.

• If hints are conflicting, they are invalid. However, the database will consider other non-conflicting hints within the same comment.

• Since hints are added as comments to the query, some clients or drivers may remove these comments, causing the hints to become ineffective.

Defining query blocks in hints

You can optionally define a query block name in a hint to specify which query block the hint applies to. For more information, see Use Query Block Name and QB_NAME Hint to Control Query Optimization.

This syntax allows you to specify a hint for an embedded view in the outer query.

The syntax for query block parameters is @queryblock, where queryblock is the identifier of the query block specified in the query. The queryblock identifier can be system-generated or user-specified. If you directly specify the hint to apply to a query block, the @queryblock parameter is ignored.

• System-generated identifiers can be generated by using the EXPLAIN statement on a query.

• You can use QB_NAME to specify a user-defined name.

Types and effects of hints

Hints in OceanBase Database are divided into two main types: global hints and query block hints.

Query block hints are further divided into QB_NAME hints, transform hints, and optimizer hints, each with different classifications based on their role in the optimizer.

Global hints

Global hints apply to the entire query, regardless of their position within the query, and have the same effect. For example, the following two queries use equivalent parallel execution hints:

select /*+ parallel(8) */ * from ( select * from t1);
select * from ( select /*+ parallel(8) */ * from t1);

Defining global hints

Global hints can be applied to specific tables or indexes, or more broadly to parts of tables or indexes within views. You can define the target of a global hint using tablespec and indexspec:

• tablespec: Defines the table to which the global hint applies. When using a hint, you must strictly use the table name or alias as shown in the query statement. If the query uses an alias for the table, the hint should also use the corresponding alias.

  [ view.[, view. ]... ] table_name
  

  Note: Even if the query includes a schema name, it should not be included in the hint.

  Note: For queries using ANSI joins, global hints specified with the tablespec clause are invalid. This is because the optimizer generates additional views during parsing. In such cases, you should use @queryblock to specify the hint for the query block.

• indexspec: Defines the index to which the global hint applies. When describing a hint, you can follow tablespec with indexspec, optionally separated by a comma.

  { index
  | ( [ table. ] column_name [ [ table. ] column_name ]... )
  }
  

Query block hints

When the OceanBase optimizer parses a query, it divides the query into multiple query blocks based on the query's structure. Each query block has its own query block name (Query Block Name). Query block names can be automatically generated by the optimizer or explicitly specified using the QB_NAME hint. For more information about query block names and the QB_NAME hint, including how to specify the scope or target of a hint using query block hints, see Query Block Name and QB_NAME Hint.

For example, in the following query, the outer query block is named qb1, and the query block within the inline view v is named qb2:

select /*+ qb_name(qb1) */ * from ( select /*+ qb_name(qb2) */ * from t1 ) v;

Query block hints are only effective within specific query blocks. There are two ways to specify a query block hint:

• Directly add the hint to the specified query block.
• Specify the query block name in the hint, with the syntax Hint_Name(@qb_name ...).

For example, in the following query, the no_merge hint is directly added to the query block qb2 and takes effect within that block. The index(@qb2 t1 idx) hint is added to the query block qb1 and takes effect within the query block qb2 by specifying the query block name:

select /*+ index(@qb2 t1 idx) qb_name(qb1) */ *
from ( select /*+ no_merge qb_name(qb2) */ * from t1 ) v;

In query block hints, besides the QB_NAME hint for specifying the current query block name, other hints play roles in different stages of the optimizer's query rewriting and plan generation: transform hints influence query rewriting behavior, while optimizer hints influence plan generation behavior.