The SQL hint syntax is as follows:
/*+ HINT_NAME */
or
/*+ HINT_NAME ( HINT_PARA )
To connect to OceanBase Database from OceanBase Client (OBClient) or a MySQL client, be sure to specify the -c parameter so that the hint takes effect after it is sent to OBServer.
The following table describes the common SQL Hints for OceanBase Database.
Hint |
Argument |
Description |
|---|---|---|
| NO_REWRITE | Indicates not to rewrite the SQL statement. | |
| READ_CONSISTENCY | weak|strong|frozen | WEAK: weak consistency read STRONG: strong consistency read FROZEN: reads data from the last major freeze |
| INDEX_HINT | [ qb_name ] table_name index_name | Specifies the index selected during table querying. |
| QUERY_TIMEOUT | int64 | Specifies the statement execution timeout value, in microseconds (us). |
| LEADING | [ qb_name ] table_name [, table _ name ] | Specifies the order in which multiple tables are joined. |
| ORDERED | Specifies the order in which the tables are displayed in SQL statements. | |
| FULL | [qb _name ] table_name | Specifies a full table scan as the access method (reads the primary key if any). |
| USE_MERGE | [qb _name ] table_name [, table_name ] | Specifies to use a MERGE algorithm for a multi-table join. |
| USE_NL | [ qb_name ] table_name [, table_name ] | Specifies that a nested-loop algorithm is used for multi-table joins. |
| USE_BNL | [qb_name] table_name [,table_name] | Indicates that a block-nested loop (BNL) algorithm is used for multi-table joins. |
| USE_HASH_AGGREGATION | [ qb_name ] | Indicates that the aggregate method is HASH AGGREGATE such as HASH GROUP BY or HASH DISTINCT. |
| NO_USE_HASH_AGGREGATION | [ qb_name ] | Indicates that the aggregate method is not HASH AGGREGATE such as HASH GROUP BY or HASH DISTINCT. |
| QB_NAME | qb_name | Specifies the name of the query block. |
| PARALLEL | int64 | Specifies the degree of parallelism of distributed execution. |