Introduction to the basic syntax and scenarios of HINTs - 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 */. The hintItems is a HINT statement for a specific operation, and multiple hintItems are separated by a comma (,).
In a Lindorm SQL statement for a wide table, 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. The default value is 120,000. The value must be greater than 0. The unit is milliseconds (ms). This parameter is 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 in the console whether the SQL engine version meets the requirement. _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. This parameter is 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, which is 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 without reporting an error. 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. This parameter is 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. The unit is milliseconds (ms). This parameter is 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. The unit is milliseconds (ms). This parameter is 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. The unit is milliseconds (ms). This parameter is supported only for `SELECT`.
_l_hot_only_	BOOLEAN	Specifies whether to query only hot data. This parameter is 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 Data Manipulation Language (DML) operation to 30,000 ms in a query that counts the table size.
```
SELECT /*+  _l_operation_timeout_(30000) */ COUNT(*) FROM t_test_ts;
```
Result:
```
+----------+
| COUNT(*) |
+----------+
| 1        |
+----------+
```
Example 2: Set the timeout period for the DML operation to 30,000 ms when you write 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 the DML operation to 30,000 ms when you delete data that meets a specific condition.
```
DELETE /*+  _l_operation_timeout_(30000) */ FROM tb WHERE c1 = 1;
```

Example 4: Use _l_force_index_ to force the 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 the query to ignore an index.

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

Result:

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

Example 6: Use _l_allow_filtering_ to filter specific data.
```
SELECT /*+ _l_allow_filtering_ */ COUNT(*) FROM tb WHERE c1 = 2;
```
Result:
```
+----------+
| COUNT(*) |
+----------+
| 1        |
+----------+
```
In this example, column 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 the 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