Overview

Hints are SQL statement comments used to pass instructions to the OceanBase Database optimizer. By using hints, you can instruct the optimizer to generate a specific execution plan. The hint syntax and types are the same in MySQL mode and Oracle mode. However, some behaviors or default values may vary depending on the mode. For more information, see the description of each hint.

In most cases, 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 a custom execution plan.

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

How to use hints

Usage and considerations

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

• Syntax for hints in statement comments:

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

• Rules for defining hints:

• Hints are a special type of SQL comment that starts with /*+ and cannot contain spaces. If the server cannot recognize a hint in an SQL statement, the optimizer will ignore the specified hint and use the default plan's execution logic.

• Only one hint can be added after each SELECT keyword, and this hint can contain multiple sub-hints. The + sign in /*+ can be followed by a space or not. If multiple hints are included in a comment, they must be separated by at least one space.

  In the following example, /*+ 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 with multiple possible hint positions, multiple hints can be used.

• Hints with spelling or syntax errors are ignored. However, the database considers 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 conflict with each other, they are invalid. However, the database considers other non-conflicting hints within the same comment.

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

Defining query blocks in hints

You can define an optional query block name in many hints 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.

Using this syntax, you can specify a hint for an embedded view in an outer query.

The syntax for query block parameters is @queryblock, where queryblock is the identifier of the specified query block in the query. queryblock can be a system-generated identifier or a user-specified identifier. If you directly specify a hint for 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 custom name.

Classification and effects of hints

OceanBase Database hints 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 type of hint is classified based on its role in the optimizer.

Global hints

Global hints apply to the entire query, regardless of their position within the query. 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 to parts of tables or indexes within views. You can use tablespec and indexspec to define the objects affected by global hints:

• tablespec: Defines the table affected by the global hint. 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 affected by the global hint. When specifying 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 it into multiple query blocks based on the query 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 inner inline view v has a query block 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 query block hints:

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