Guidelines for using hints

This topic describes the guidelines for using hints in OceanBase Database.

Hints should be used sparingly, only after the optimizer has evaluated the query plan without hints and after statistics have been collected for the relevant tables. Changes in database conditions or improvements in query performance in later versions may significantly impact the performance of code that includes hints.

Introduction to HINT

• The cost-based optimizer, similar to Oracle's HINT.

• If the server does not recognize the HINT in the SQL statement, it will be ignored without any error.

• HINT only affects the logic of the optimizer generating plans, not the semantics of the SQL statement itself.

• HINT is a mechanism that allows the optimizer to generate a specific plan. Generally, the optimizer selects the best execution plan for the user's query without the need for HINTs. However, in some scenarios, the optimizer's generated plan may not meet the user's requirements, necessitating the use of HINTs to explicitly specify a particular execution plan.

HINT syntax

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

• A statement block can contain only one comment with HINT, and this comment must follow the SELECT, UPDATE, INSERT, MERGE, or DELETE keyword.

• From a syntactic perspective, HINT is a special SQL comment. The difference is that a '+' is added after the left delimiter of the comment ('/*' symbol). Since it is a comment, if the server cannot recognize the HINT in the SQL statement, the optimizer will ignore the user's HINT and use the default plan generation logic. Additionally, it is important to note that HINT only affects the logic of the optimizer generating plans, not the semantics of the SQL statement.

• Some rules to keep in mind when defining HINTs:

  • The '+' makes the database interpret the comment as a HINT list. The '+' must immediately follow the left delimiter of the comment, with no space allowed.

  • The space between the '+' and the HINT text is optional. If the comment contains multiple HINTs, they must be separated by at least one space or a comma.

  • HINTs with spelling or syntax errors will be ignored. However, the database will consider other correctly specified HINTs within the same comment.

  • HINTs that do not follow the DELETE, INSERT, MERGE, SELECT, or UPDATE keywords are invalid.

• In addition to the HINT concept, OceanBase Database differs from Oracle in the following aspects:

  • Oracle leading HINT: When an invalid table is encountered, whether the HINT takes effect is calculated, leading to unpredictable behavior, where it may be effective in some cases and completely ineffective in others.

Examples of HINT usage

• Without parameters, such as /*+ KAKA */.

• With parameters, such as /*+ HAHA(param) */.

• Multiple HINTs can be written in the same comment, separated by commas or at least one space, such as /*+ KAKA, HAHA(param)*/.

• For SELECT statements, the HINT must be immediately after the SELECT keyword, before any other keywords. For example: SELECT /*+ KAKA */.

• For UPDATE and DELETE statements, the HINT must be immediately after the UPDATE or DELETE keyword. For example: UPDATE /*+ KAKA */, DELETE /*+ KAKA */.

Common HINT types

For more information about HINTs, see SQL optimization.