Overview

A hint is a comment in an SQL statement that passes instructions to the OceanBase Database optimizer. You can use hints to make the optimizer generate a specified execution plan.

Generally, a hint is not required because the optimizer selects the best execution plan for a query. In some scenarios, the execution plan generated by the optimizer may not meet user requirements. In this case, you need to add a hint to specify and generate a specific execution plan.

It is important to note that you need to only consider using hints with caution after collecting the relevant table statistics and evaluating the optimizer's plan using the EXPLAIN PLAN statement without any hints. Changes to database conditions or enhancements to query performance in future versions may cause the hints in your code to have a significant impact on performance.

How to use hints

Considerations

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

• The syntax for using hints in statement comments is as follows:

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

• Some rules to note when defining hints:

• Syntactically, a hint is a special type of SQL comment that starts with /*+ and does not allow spaces after the /*. If the server cannot recognize the hint in the SQL statement, the optimizer will ignore the user-specified hint and use the default execution plan.

• Only one hint comment can be added after each SELECT or similar keyword, but this hint comment can contain multiple hints. There may or may not be a space between the + in /*+ and the hint text. If multiple hints are included in the comment, they must be separated by at least one space.

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

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

• For queries where multiple positions allow hints, you can use multiple hint comments within the query.

• Hints that contain spelling or syntax errors will be ignored. However, the database will still consider other correctly specified hints within the same comment.

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

• If hints within a comment conflict with each other, those hints are invalid. However, the database will still consider other non-conflicting hints within the same comment.

• Because hints are added as comments in queries, some clients or drivers may remove comments from queries, causing the hints to become ineffective.

Define a query block in a hint

You can specify which query block a hint applies to by defining an optional query block name in many hints. For more information, see Using query block name and QB_NAME hint to control query optimization.

Using this syntax allows you to specify a hint in the outer query that applies to an embedded view.

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

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

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

Classification and feature of hints

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

Query block hints are further subdivided into QB_NAME hints, transform hints, and optimizer hints. Each type of hint has different classifications based on its feature within the optimizer.

Global hints

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

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

Definition of global hints

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

• tablespec: Defines the table that the global hint applies to. When using a hint, you must strictly follow the table name or alias as it appears in the query. If the query uses a table alias, the hint should also use the corresponding alias.

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

  Note that even if the schema name is included in the query, it should not be included in the hint.

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

• indexspec: Defines the index that the global hint applies to. When specifying a hint, indexspec can follow tablespec, and separating the table name and index name with a comma is optional.

  { 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 its structure, with each query block having its own query block name. The query block name 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, such as how to use query block hints to specify the scope or target of a hint, 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;

A query block hint is only effective within the specific query block where it is defined. There are two ways to specify the effective scope of a query block hint:

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

For example, in the following query, the no_merge hint is added directly to query block qb2 and is effective within that block; the index(@qb2 t1 idx) hint is added to query block qb1 and is made effective within 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;

Within query block hints, apart from the QB_NAME hint which is used to specify the name of the current query block, other hints take effect at different stages of the optimizer: transform hints influence query rewrite behavior, while optimizer hints influence plan generation behavior.