Syntax and scenarios of hints in LindormTable - Lindorm - Alibaba Cloud Documentation Center

Hints are supplementary to the SQL syntax and play an important role in queries performed on databases. You can use hints in a SQL statement to change the method in which the SQL statement is executed. LindormTable supports hints in SQL statements. For example, you can use hints in LindormTable SQL statements to manage multiple versions of data. This topic describes the syntax and scenarios of hints in LindormTable.

Prerequisites

The version of LindormTable is 2.3.1 or later. For more information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Limits

Hints can be used only after the INSERT, UPSERT, DELETE, and SELECT keywords in SQL statements.
If the LindormTable version of your instance is 2.5.2.1 or earlier, hints cannot be used in SQL statements that are executed to perform complex queries, such as subqueries and group queries.

Syntax

hintExpression ::= /*+ hintItems */

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

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

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

Note

Hints are used in the /*+ hintItems */ format, in which hintItems indicates operation-specific hints. Multiple hintItems are separated by commas (,).
Hints can be used only after the INSERT, UPSERT, DELETE, and SELECT keywords in LindormTable SQL statements. For example, hints in the following statements are invalid: UPSERT INTO /*+ _l_ts_(3000) */ t_test_ts(c1, c3) VALUES (1, 'c3');.

Parameters of hintOptions

Parameter	Type	Description
_l_operation_timeout_ Note This name of this parameter is operationtimeout in Lindorm whose version is earlier than 2.5.2.1.	INT	The timeout period of the DML operation. Default value: 120000. The value of this parameter must be larger than 0. Unit: millisecond. This parameter is applicable only to the hints in UPSERT, DELETE, and SELECT statements. Note _l_operation_timeout_ can be used together with other parameters in a hint. Separate different parameters with commas (,). Example: `SELECT /+ _l_operation_timeout_(1000), _l_force_index_('idx1') / * from test;`.
_l_force_index_	STRING	Specifies that indexes must be used. This parameter is applicable only to the hints in SELECT statements. Note The _l_force_index_ parameter cannot be specified together with the _l_ignore_index_ parameter.
_l_ignore_index_	N/A	Specifies that indexes are not used to query data. You can specify this parameter in the hint when you want to compare the performance of between queries that use indexes and queries that do not use indexes. You do not need to specify a value for this parameter when you use it in a hint. This parameter is applicable only to the hints in SELECT statements. Note The _l_ignore_index_ parameter cannot be specified together with the _l_force_index_ parameter.
_l_allow_filtering_	N/A	Specifies whether to allow inefficient queries that scan all data in the table. If this parameter is not specified in a query, an error is returned when the WHERE conditions in the query do not contain primary keys. If you specify this parameter in a hint, the query can be executed with no errors reported. You do not need to specify a value for this parameter when you use it in a hint. This parameter is applicable only to the hints in SELECT statements.
_l_versions_	INT	Specifies that the latest N versions of the data you query are returned. The value of this parameter must be larger than 0. This parameter is applicable only to the hints in SELECT statements.
_l_ts_	BIGINT	The timestamp that indicates the time when data is inserted to non-primary key columns or the time when data in non-primary key columns is queried. The timestamp can be used for data versioning. The value of this parameter must be larger than 0. Unit: milliseconds. This parameter is applicable only to the hints in UPSERT and SELECT statements.
_l_ts_min_	BIGINT	The minimum timestamp of the data you want to query. The value of this parameter must be larger than 0. Unit: milliseconds. This parameter is applicable only to the hints in SELECT statements.
_l_ts_max_	BIGINT	The maximum timestamp of the data you want to query. The value of this parameter must be larger than 0. Unit: milliseconds. This parameter is applicable only to the hints in SELECT statements.
_l_hot_only_	BOOLEAN	Specifies whether only hot data is queried. This parameter is applicable only to the hints in SELECT statements. You can set this parameter to one of the following values: true: only hot data in the table is queried. false: all data in the table is queried. Note To specify that all data in the table is queried, you can either set _l_hot_only_ to `false` or do not specify this parameter in the hint.

Examples

Example 1: Query the number of rows in a table and set the timeout period of DML operations to 30,000 ms.
```
SELECT /*+  _l_operation_timeout_(30000) */ COUNT(*) FROM t_test_ts;
```
The following result is returned:
```
+----------+
| COUNT(*) |
+----------+
| 1        |
+----------+
```
Example 2: Write data to a row in a table and set the timeout period of DML operations to 30,000 ms.
```
UPSERT /*+  _l_operation_timeout_(30000) */ INTO t_test_ts(c1, c2, c3) values(1,2,3);
```
Example 3: Delete data that matches the conditions and set the timeout period of DML operations to 30,000 ms.
```
DELETE /*+  _l_operation_timeout_(30000) */ FROM tb WHERE c1 = 1;
```

Example 4: Specify _l_force_index_ to forcibly use an index in the query.

SELECT /*+  _l_force_index_('idx1') */ COUNT(*) FROM tb; // 'idx1'  indicates the index created for the table.

The following result is returned:

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

Example 5: Specify _l_ignore_index_ to perform the query without using the index.
```
SELECT /*+  _l_ignore_index_ */ COUNT(*) FROM tb;
```
The following result is returned:
```
+----------+
| COUNT(*) |
+----------+
| 1        |
+----------+
```
Example 6: Specify _l_allow_filtering_ to allow queries in which primary keys are not contained in the conditions.
```
SELECT /*+ _l_allow_filtering_ */ COUNT(*) FROM tb WHERE c1 = 2;
```
The following result is returned:
```
+----------+
| COUNT(*) |
+----------+
| 1        |
+----------+
```
The c1 column specified in the query condition is not a primary key column nor an index column. No error is returned after you specify the _l_allow_filtering_ parameter in the hint.
Example 7: Specify _l_ts_ to configure the timestamp of the data that you want to insert.
```
UPSERT /*+ _l_ts_(3000) */ INTO t_test_ts(c1, c3) VALUES (1, 'c3');
```

Example 8: Specify _l_versions_ to query the latest version of data.

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

The following result is returned:

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

Scenarios

You can use hints in the following scenarios:

Use hints to query hot data