Hints are important supplementary to the SQL syntax in 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. Hints cannot be used after other keywords. 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

ParameterTypeDescription
_l_operation_timeout_
Note This name of this parameter is operationtimeout in Lindorm 2.5.2.1 and earlier versions.
INTThe timeout period of the DML operation. Default value: 60000. 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_STRINGSpecifies 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/ASpecifies 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/ASpecifies 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_INTSpecifies 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_BIGINTThe 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: millisecond.This parameter is applicable only to the hints in UPSERT and SELECT statements.
_l_ts_min_BIGINTThe minimum timestamp of the data you want to query. The value of this parameter must be larger than 0. Unit: millisecond.This parameter is applicable only to the hints in SELECT statements.
_l_ts_max_BIGINTThe maximum timestamp of the data you want to query. The value of this parameter must be larger than 0. Unit: millisecond.This parameter is applicable only to the hints in SELECT statements.
_l_hot_only_BOOLEANSpecifies whether only hot data is queried. This parameter is applicable only to the hints in SELECT statements.
Valid 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 DML operations whose timeout period is longer than 30,000 ms. 
    SELECT /*+  _l_operation_timeout_(30000) */ COUNT(*) FROM t_test_ts;
    The following result is returned:
    +----------+
| COUNT(*) |
+----------+
| 1        |
+----------+
  • Example 2: Specify the timeout period for DML operations performed on the inserted data. 
    UPSERT /*+  _l_operation_timeout_(30000) */ INTO t_test_ts(c1, c2, c3) values(1,2,3);
  • Example 3: Delete data on which DML operations with a timeout period longer than 30,000 ms are performed based on the specified condition. 
    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 name of 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