Introduction to the basic syntax and scenarios of HINTs-Lindorm(Lindorm)-阿里云帮助中心

Prerequisites

The wide table engine must be version 2.3.1 or later. To view or upgrade the current version, see Wide table engine version guide and Upgrade a minor version.

Limits

A HINT must be used after the INSERT, UPSERT, DELETE, or SELECT keyword.
For wide table engine versions earlier than 2.5.2.1, HINTs are supported only for simple queries and are not supported for complex queries such as subqueries or group queries.

HINT syntax

hintExpression ::= /*+ hintItems */

hintItems ::= hintItem (',' hintItem )*

hintItem ::= identifier ('(' hintOption ( ',' hintOption)* ')')?

identifier ::=  ( [A-Z] | '_' ) ( [A-Z] | [0-9] | '_' | '@' | ':')*

Note

A HINT uses the format /*+ hintItems */. hintItems is a HINT statement for a specific operation. Multiple hintItems are separated by commas (,).
In a Lindorm wide table SQL statement, a HINT can be specified only after the INSERT, UPSERT, DELETE, or SELECT keyword. The following example is invalid: UPSERT INTO /*+ _l_ts_(3000) */ t_test_ts(c1, c3) VALUES (1, 'c3');.

hintOption parameter descriptions

Parameter	Data type	Description
_l_operation_timeout_ Note For wide table engine versions earlier than 2.5.2.1, this parameter is named operationtimeout.	INT	The timeout period for a DML operation, in milliseconds. Default value: 120,000. The value must be greater than 0. Supported for `UPSERT`, `DELETE`, `UPDATE`, and `SELECT`. Important To use this parameter in `DELETE` or `UPDATE` statements, the SQL engine version must be 2.8.4.4 or later. You can check the SQL engine version in the console. _l_operation_timeout_ can be combined with other HINT parameters, separated by a comma (,). For example: `SELECT /+ _l_operation_timeout_(1000), _l_force_index_('idx1') / * from test;`.
_l_force_index_	STRING	Forces the use of a specified index. Supported only for `SELECT`. Note The _l_force_index_ parameter cannot be used with the _l_ignore_index_ parameter.
_l_ignore_index_	Not applicable	Ignores indexes and queries data directly from the data table, useful for comparing query performance with and without an index. No value is required. Supported only for `SELECT`. Note The _l_ignore_index_ parameter cannot be used with the _l_force_index_ parameter.
_l_allow_filtering_	Not applicable	By default, queries that filter on non-primary key columns return an error. This parameter allows full table scans on such columns. No value is required. Supported only for `SELECT`.
_l_versions_	INT	Returns the latest N versions of data in the query results. The value must be greater than 0. Supported only for `SELECT`.
_l_ts_	BIGINT	The timestamp for multi-version management, used when writing or querying non-primary key columns. The value must be greater than 0. Unit: milliseconds. Supported for `UPSERT` and `SELECT`.
_l_ts_min_	BIGINT	The minimum timestamp for multi-version management, used to filter query results. The value must be greater than 0. Unit: milliseconds. Supported only for `SELECT`.
_l_ts_max_	BIGINT	The maximum timestamp for multi-version management, used to filter query results. The value must be greater than 0. Unit: milliseconds. Supported only for `SELECT`.
_l_hot_only_	BOOLEAN	Specifies whether to query only hot data. Supported only for `SELECT`. Valid values: true: Queries only the hot data in the table. false: Queries all data in the table. Note Setting `_l_hot_only_` to `false` has the same effect as not using this HINT.

Examples

Example 1: Set the timeout period for a DML operation to 30,000 ms in a row-count query.

SELECT /*+  _l_operation_timeout_(30000) */ COUNT(*) FROM t_test_ts;

Result:

+----------+
| COUNT(*) |
+----------+
| 1        |
+----------+

Example 2: Set the timeout period for a DML operation to 30,000 ms when writing a row of data.
```
UPSERT /*+  _l_operation_timeout_(30000) */ INTO t_test_ts(c1, c2, c3) values(1,2,3);
```
Example 3: Set the timeout period for a DML operation to 30,000 ms when deleting data that meets a specific condition.
```
DELETE /*+  _l_operation_timeout_(30000) */ FROM tb WHERE c1 = 1;
```

Example 4: Use _l_force_index_ to force a query to use a specific index.

SELECT /*+  _l_force_index_('idx1') */ COUNT(*) FROM tb;   // 'idx1' is the name of an existing index.

Result:

+----------+
| COUNT(*) |
+----------+
| 1        |
+----------+

Example 5: Use _l_ignore_index_ to force a query to ignore indexes.

SELECT /*+  _l_ignore_index_ */ COUNT(*) FROM tb;

Result:

+----------+
| COUNT(*) |
+----------+
| 1        |
+----------+

Example 6: Use _l_allow_filtering_ to allow filtering on a non-primary key column.
```
SELECT /*+ _l_allow_filtering_ */ COUNT(*) FROM tb WHERE c1 = 2;
```
Result:
```
+----------+
| COUNT(*) |
+----------+
| 1        |
+----------+
```
In this example, c1 is neither a primary key nor an index column. The HINT allows the query to run without an error.

Example 7: Use _l_ts_ to specify a timestamp for a row to be written.

UPSERT /*+ _l_ts_(3000) */ INTO t_test_ts(c1, c3) VALUES (1, 'c3');

Example 8: Use _l_versions_ to retrieve the latest version of the data.

SELECT /*+ _l_versions_(1) */ c1, c3, c3_l_ts FROM t_test_ts;

Result:

+----+----+---------+
| c1 | c3 | c3_l_ts |
+----+----+---------+
| 1  | c3 | 3000    |
+----+----+---------+

Scenarios

HINTs apply to the following scenarios:

Query hot data using a HINT