All Products
Search
Document Center

Hint overview

Last Updated: Jun 18, 2021

Hints are comments that are located in SQL statements and pass instructions to the ApsaraDB for OceanBase optimizer or server. The optimizer or the server can generate specific execution plans based on hints. In general, the optimizer chooses the optimal execution plan for your query without the need to use hints. However, in some scenarios, the optimizer-generated execution plan may not meet your requirements. In these scenarios, you can use hints to specify the execution plans that are to be generated.

We recommend that you do not use hints if possible. Use hints only after you have collected statistics about the relevant tables and have executed the EXPLAIN PLAN statement to evaluate the optimizer-generated plan that is not affected by hints. Query performance improvements in subsequent versions and changes to database conditions may cause hints in your code to have a significant impact on performance.

Use hints

In a statement block, only one comment can contain hints and the comment must follow the SELECT, UPDATE, INSERT, MERGE, or DELETE keyword.

The following hint syntax applies to comments in statement blocks:

/*+[hint text]*/

In terms of syntax, hints are a special type of SQL comments. The difference between comments and hints is that their marks are different. If a plus sign (+) is appended to the left mark of a comment, the comment is interpreted as a hint. If the server cannot recognize hints in SQL statements, the optimizer ignores the specified hints and uses the logic that is generated by the default execution plan. In addition, take note of the following point: Hints affect only the logic of optimizer-generated plans and do not affect the semantics of SQL statements.

Take note of the following rules when you define hints:

  • The plus sign (+) causes the database to interpret a comment as a list of hints. The plus sign (+) must immediately follow the left mark of the comment. No white-space character is allowed.

  • The white-space character between the plus sign (+) and the hint text is optional. If a comment contains multiple hints, use at least one white-space character to separate hints.

  • Hints that contain spelling or syntax errors are ignored. However, the database does not ignore the other hints that are correctly specified in the same comment.

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

  • In a combination of hints, the hints that conflict with each other are invalid. However, the database does not ignore the other hints in the same comment.

  • Hints are invalid when the database environment uses PostgreSQL version 1 or SQL Server version 1. For example, when the database environment uses triggers in Forms version 3, hints are invalid.

Specify query blocks in hints

You can specify an optional query block name in multiple hints to specify the query block on which the hint takes effect. In an outer query, you can use this syntax to specify a hint that applies to an inline view.

The parameters of a query block use the @queryblock syntax. In this syntax, queryblock is the identifier of the query block that is specified in your query. The queryblock identifier can be customized or system-generated. When you directly specify a hint to be applied in a query block, @queryblock is ignored.

  • You can obtain the system-generated identifier by executing the EXPLAIN PLAN statement for your query. You can obtain the names of pre-transformation query blocks by executing the EXPLAIN PLAN statement for your query that uses the NO_QUERY_TRANSFORMATION hint.

  • You can use QB_NAME to specify a custom name.

Specify global hints

Many hints can be applied to specific tables or indexes and more globally to the tables in a view or to some columns that are part of indexes. These global hints are specified by using the tablespec and indexspec syntax elements.

tablespec uses the following syntax:

[ view.[ view. ]... ]table

You must specify the table that you want to access exactly as it appears in the statement. If the statement uses a table alias, use the alias instead of the table name in the hint. However, even if the schema name appears in the statement, do not include the schema name in the table name that is used in the hint.

Notice

If you use the tablespec clause to specify global hints, the global hints do not take effect on the queries that use ANSI joins. This is because the optimizer generates additional views in the parsing process. Instead, you can use @queryblock to specify the query block to which the hint applies.

indexspec uses the following syntax:

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

When tablespec is followed by indexspec in the description of a hint, commas (,) that separate table names and index names are allowed but not required. Commas (,) that separate multiple occurrences of indexspec are also allowed but not required.