This topic describes the usage guidelines for hints in OceanBase AI Database.
Use hints sparingly. Consider applying them only after you have collected statistics on the relevant tables and evaluated the optimizer's plan for the statement without a hint. Changes to database conditions and future enhancements to query performance may significantly impact the performance of hints in your code.
Introduction to HINTs
A cost-based optimizer, similar to Oracle hints.
If the server does not recognize a hint in an SQL statement, it will be ignored without returning an error.
A hint only affects the logic used by the database optimizer to generate execution plans, not the semantics of the SQL statement itself.
A hint is a mechanism that allows you to direct the optimizer to generate a specific execution plan. Normally, the optimizer selects the optimal execution plan for your query without requiring explicit hints. However, in some scenarios, the plan generated by the optimizer may not meet your requirements. In such cases, you can use hints to explicitly instruct the optimizer to generate a particular execution plan.
Syntax of HINT
{CREATE|DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ [HINT_text][hin_text]... */
A statement block can contain only one comment with a hint, and that comment must follow the SELECT, UPDATE, INSERT, MERGE, or DELETE keyword.
A hint is syntactically a special SQL comment, except that it starts with
'+'instead of the standard'/*'symbol. As a comment, if the server cannot recognize a hint in an SQL statement, the optimizer will choose to ignore the user's hint and use the default plan generation logic. It should also be noted that hints only affect the logic by which the optimizer generates plans, not the semantics of the SQL statements.Some rules to note when defining a hint:
The plus sign (
'+') instructs the database to interpret the comment as a hint list. The plus sign must follow immediately after the left marker of the comment, with no spaces allowed.A space between the plus sign (
+) and the hint text is optional. If a comment contains multiple hints, separate them with at least one space or a comma.Comments containing spelling or syntax errors will be ignored. However, the database will consider other correctly specified hints within the same comment.
Hints that are not followed by the DELETE, INSERT, MERGE, SELECT, or UPDATE keywords are invalid.
In addition to the inconsistent concept of hints, OceanBase AI Database and Oracle also differ in the following aspects:
- Oracle leading HINT: When an non-existent table is referenced, whether this hint takes effect is determined through calculation, leading to unpredictable behavior. It may be effective in some cases but completely ineffective in others.
Sample usage of HINT
Statements without parameters, such as
/*+ KAKA */.Lines containing parameters, such as
/*+ HAHA(param) */.Multiple hints can be written in the same comment, separated by commas or at least one space, for example,
/*+ KAKA, HAHA(param)*/.The hint for the SELECT statement must follow immediately after the SELECT keyword and precede all other words. For example:
SELECT /*+ KAKA */.The hint for UPDATE and DELETE statements must follow immediately after the UPDATE or DELETE keyword. For example:
UPDATE /*+ KAKA */,DELETE /*+ KAKA */.
Common HINT types
Hint name |
Hint parameters |
Hint semantics |
|---|---|---|
| NO_REWRITE | SQL statements are not rewritten. | |
| READ_CONSISTENCY | weak|strong|frozen | weak: weak-consistency read strong: strong-consistency read frozen: reads data from the last freeze point |
| INDEX_HINT | [ qb_name ] table_name index_name | Specifies the index to be used when querying the table. |
| QUERY_TIMEOUT | int64 | Specifies the timeout period for statement execution, in microseconds (us). |
| LEADING | [ qb_name ] table_name [, table_name ] | Specifies the order for joining multiple tables. |
| ORDERED | Specify the multi-table join order as the order in which the tables appear in the SQL statement. | |
| FULL | [qb_name] table_name | Specify the table access method as a full table scan (reads the primary key if present). |
| USE_MERGE | [qb_name] table_name [,table_name] | Specifies to use the MERGE algorithm for multi-table joins. |
| USE_NL | [ qb_name ] table_name [, table_name] | Specifies to use the NEST LOOP algorithm for multi-table joins. |
| USE_BNL | [qb_name] table_name [,table_name] | Specifies to apply the block nest loop algorithm for multi-table joins. |
| USE_HASH_AGGREGATION | [ qb_name] | Specifies to use HASH AGGREGATE for aggregate methods, such as HASH GROUP BY and HASH DISTINCT. |
| NO_USE_HASH_AGGREGATION | [ qb_name ] | Specifies that the aggregate method does not use HASH AGGREGATE, but uses MERGE GROUP BY or MERGE DISTINCT. |
| QB_NAME | [ qb_name] | Specifies the name of the query block. |
| PARALLEL | int64 | Specifies the degree of parallelism for distributed execution. |