A hint is a comment in an SQL statement used to instruct the OceanBase AI Database optimizer. Hints can be used to make the optimizer generate a specific execution plan.
Normally, the optimizer selects the optimal execution plan for user queries without the need for hints. However, when the optimizer-generated plan may not meet user requirements, you can use hints to explicitly specify and generate a special execution plan.
Note that you should use hints with caution only after collecting statistics on the relevant tables and evaluating the optimizer plan using the EXPLAIN PLAN statement in the absence of hints. Changes to database conditions and enhancements to query performance in later versions may significantly impact the performance of the hints in your code.
How to use hints
Usage instructions and considerations for hints
A statement can contain only one hint, which must follow the CREATE, SELECT, UPDATE, INSERT, REPLACE, or DELETE keyword. A hint only affects the logic of the execution plan generated by the optimizer, not the semantics of the SQL statement.
- The syntax for a hint in a statement comment is as follows:
{CREATE|DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ hint_text [,hint_text...] */
Some rules to note when defining hints:
A hint is syntactically a special SQL comment, starting with
/*+without any spaces. If the server cannot recognize a hint in an SQL statement, the optimizer will choose to ignore the user-specified hint and use the execution logic generated by the default plan.
Each keyword such as
SELECTcan be followed by only one hint block, which can contain multiple hints. In a hint comment starting with/*+, you can include spaces between the+and the hint text, or omit them altogether. If the comment contains multiple hints, separate them with at least one space.In the following examples,
/*+ 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 locations where hints can be added, you can use multiple hint clauses in the query.
A hint that contains a spelling or syntax error will be ignored. However, the database will consider other correctly specified hints used within the same comment.
Hints that are not followed by the
CREATE,DELETE,INSERT,REPLACE,SELECT, orUPDATEkeywords are invalid.A hint becomes invalid if it conflicts with another hint. However, the database will consider applying any other non-conflicting hints within the same comment.
Because hints are added to queries as comments, some clients/drivers may remove these comments during query parsing, causing the hints to become invalid.
Define query blocks in hints
You can define an optional query block name in many hints to specify the query block for which the hint applies. For more information, see Control query optimization using the Query Block Name and QB_NAME hint
This syntax allows you to specify a hint for embedded views in external queries.
The syntax for querying 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. When you directly specify the hint to apply within a query block, the @queryblock directive is ignored.
System-generated identifiers can be generated by using the
EXPLAINstatement for queries.You can use QB_NAME to specify a user-defined name.
Types and uses of hints
Hint types in OceanBase AI Database can be categorized 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 is categorized differently based on its role within the optimizer.
Global hint
Global hints apply to the entire query and produce the same effect regardless of their position within the query. For example, the parallel execution hint used in the following two queries is equivalent:
select /*+ parallel(8) */ * from ( select * from t1);
select * from ( select /*+ parallel(8) */ * from t1);
Definition of global hints
Global hints can be applied to specific tables or indexes, or more broadly to a subset of tables or indexes within a view. You can use tablespec and indexspec to define the target of the global hint:
tablespec: Defines the table for which the global hint applies. When using a hint, you must strictly follow the table name or alias 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_nameNote: Even if the schema name is included in the query, it should not be included in the hint.
Note: For queries using ANSI connections, global hints specified using the
tablespecclause 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 for which the global hint applies. When describing a hint,indexspeccan followtablespec, and it is optional to separate the table name and index name with a comma.{ index | ( [ table. ] column_name [ [ table. ] column_name ]... ) }
Query Block Hint
When the OceanBase optimizer parses a query, it divides it into multiple query blocks based on its structure. Each query block has its own Query Block Name (QBName). The QBName can be automatically generated by the optimizer or explicitly specified using the QB_NAME hint. For more information about QBNames and the QB_NAME hint, such as specifying the scope or target of the hint, see Control query optimization using the Query Block Name and QB_NAME hint.
For example, in the following query, the name of the outer query block is qb1, and the name of the query block within the inline view v is qb2:
select /*+ qb_name(qb1) */ * from ( select /*+ qb_name(qb2) */ * from t1 ) v;
Query block hints are only valid within the specific query block. There are two ways to specify when a query block hint takes effect:
- Directly add a 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 takes effect within that block. The index(@qb2 t1 idx) hint is added to query block qb1 and, by specifying the query block name, takes effect within query block qb2:
select /*+ index(@qb2 t1 idx) qb_name(qb1) */ *
from ( select /*+ no_merge qb_name(qb2) */ * from t1 ) v;
Among query block hints, except for the QB_NAME hint which specifies the name of the current query block, other hints operate at different stages of the optimizer's query rewrite and plan generation process: the Transform hint intervenes in query rewrite behavior, while the Optimizer hint intervenes in plan generation behavior.