Hint

This topic describes the requirements and suggestions on the use of hints in OceanBase Database.

We recommend that you use a hint only after you collect the statistics of the related table and use statements to evaluate the hint-free plan selected by the optimizer. Database condition changes and query performance enhancements in subsequent versions may result in a significant impact of the hints on the performance.

Overview

• OceanBase Database provides a cost-based optimizer and allows you to use hints in a similar way as in an Oracle database.

• If the server cannot recognize the hints in the SQL statements, the server directly ignores the hints without reporting an error.

• Hints affect only the logic used by the database optimizer to generate plans but not the semantics of the SQL statements.

• The optimizer can use hints to generate specific plans. Generally, a hint is not required because the optimizer selects the best execution plan for a query. In some scenarios, however, the execution plan generated by the optimizer may not meet your requirements. In this case, you can add hints to your query to generate a specific execution plan.

Syntax

{DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ [HINT_text][hin_text]... */

• A compound statement can contain only one hint comment, and this comment must follow the SELECT, UPDATE, INSERT, REPLACE, or DELETE keyword.

• A hint is a special SQL comment in terms of syntax, except that a plus sign (+) is added to the opening tag (/*) of the comment. As a hint is a comment, the optimizer ignores it and uses the default plan, if the server does not recognize hints in the SQL statement. In addition, a hint only affects the logic of the execution plan generated by the optimizer. The semantics of the SQL statement remains unaffected.

• Take note of the following rules when you specify a hint:

  • OceanBase Database interprets a comment that starts with a plus sign (+) as a hint list. The plus sign must immediately follow the opening tag of the comment, and no space is allowed.

  • A space between the plus sign (+) and the hint text is optional. If a comment contains multiple hints, use a comma (,) or at least one space between two hints.

  • A hint is ignored when it contains spelling or grammatical errors. However, OceanBase Database will consider using other correctly specified hints in the same comment.

  • Hints not following the DELETE, INSERT, MERGE, SELECT, or UPDATE keyword are invalid.

• Hints in OceanBase Database are not the same as those in Oracle Database.

  • If a LEADING hint is used in an Oracle database, but the specified table does not exist, the behavior of the hint is uncertain and depends on specific calculations. As a result, the hint takes effect in some cases and becomes invalid in other cases.

Examples

• A hint may contain no parameters, for example, /*+ KAKA */.

• A hint can contain parameters, for example, /*+ HAHA(param) */.

• Multiple hints can be included in the same comment and separated with commas (,) or at least one space, for example, /*+ KAKA, HAHA(param)*/.

• The hint in a SELECT statement must be placed right after the keyword SELECT and before other words, for example, SELECT /*+ KAKA */.

• The hint in an UPDATE or DELETE statement must be placed right after the keyword UPDATE or DELETE, for example, UPDATE /*+ KAKA */ and DELETE /*+ KAKA */.

Frequently used hints

For more information, see SQL tuning.