Overview

A hint is a comment in an SQL statement that passes instructions to the OceanBase Database optimizer. You can use hints to make the optimizer generate a specified execution plan.

Generally, a hint is not required because the optimizer selects the best execution plan for a query. In some scenarios, the execution plan generated by the optimizer may not meet user requirements. In this case, you need to add a hint to specify and generate a specific execution plan.

However, we recommend that you use as few hints as possible. For example, when statistics of related tables are collected and no hint is specified, we recommend that you use the EXPLAIN PLAN statement to evaluate the plan of the optimizer before you consider using a hint. Changing database conditions and enhancing query performance in subsequent versions may result in a significant impact on the performance.

Use of hints

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

Syntax for hints in statement comments:

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

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

Rules to be aware of 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, separate them with at least one space.

• 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, REPLACE, SELECT, or UPDATE keyword are invalid.

• If hints in a combination conflict with each other, the hints are invalid. However, OceanBase Database will consider using other nonconflicting hints in the same comment.

Define a query block in a hint

You can define a query block that the hint applies to by specifying the query block name in the hint.

You can use this syntax to specify the hint that applies to an embedded view in an external query.

The syntax for the query block parameter is @queryblock, where queryblock is the identifier of the query block specified in the query. A queryblock identifier can be system-generated or user-specified. When you directly specify the hint to apply in the query block, @queryblock will be ignored.

• You can use EXPLAIN to generate a system-generated identifier.

• You can also use QB_NAME to specify the name of the query block.

Define a global hint

Many hints apply to a specific table or index. They can also apply globally to tables of a view or to columns in an index. Syntax elements tablespec and indexspec define these global hints.

Syntax of tablespec:

[ view.[,view. ]... ] table_name

You must specify the tables to be accessed exactly as shown in the statement. If the statement uses an alias of a table, also use the alias instead of the table name in the hint. However, even if the schema name is specified in the statement, do not include the schema name in the table name in the hint.

Syntax of indexspec:

{ index
| ( [ table. ]column_name [ [ table. ]column_name ]...)
}

In the description of a hint, if tablespec is followed by indexspec, you can, but do not have to, use a comma to separate the table name from the index name. Likewise, you can, but do not have to, use commas to separate multiple indexspec elements.