Lindorm:Use hints to query hot data - Lindorm

SQL hints let you control which storage tier LindormTable scans during a SELECT query. When your Lindorm instance uses tiered storage, recent data lives in hot storage and older data is archived to cold storage. Querying only hot storage avoids the higher latency of cold storage scans — useful for workloads that only need recent data, such as real-time dashboards or alerting pipelines.

Applicable versions

Hints require LindormTable 2.3.1 or later.

To check or upgrade your LindormTable version, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Prerequisites

Before you begin, ensure that you have:

Capacity storage enabled for your Lindorm instance. See Enable Capacity storage

How it works

Embed a hint comment (/*+ ... */) immediately after SELECT to tell LindormTable which storage tier to read from:

Hint	Behavior	When to use
`/+ _l_hot_only_ /`	Reads only hot storage	Queries that need recent data and must avoid cold storage latency
`/+ _l_hot_only_(true) /`	Reads only hot storage (equivalent to above)	Same as above; explicit form
`/+ _l_hot_only_(false) /`	Reads all data — hot and cold (equivalent to no hint)	When you need full historical data

Important

Hints cannot restrict queries to cold data only. Only hot-only and all-data modes are supported.

Set up test data

The examples below use a table with version retention set to 5. Run the following statements to create the table and insert test rows with explicit timestamps using the _l_ts_() hint:

-- Create a test table and set the number of retained versions to 5.
CREATE TABLE t_test_versions_2 (c1 INT , c2 INT, c3 VARCHAR(50), PRIMARY KEY(c1)) WITH(VERSIONS='5');

-- Insert data with explicit timestamps.
UPSERT /*+ _l_ts_(1000)  */ INTO t_test_versions_2(c1, c3) VALUES (1, '11');
UPSERT /*+ _l_ts_(2001)  */ INTO t_test_versions_2(c1, c3) VALUES (1, '22');
UPSERT /*+ _l_ts_(1000)  */ INTO t_test_versions_2(c1, c2) VALUES (1, 1);
UPSERT /*+ _l_ts_(2001)  */ INTO t_test_versions_2(c1, c2) VALUES (2, 1);
UPSERT /*+ _l_ts_(2002)  */ INTO t_test_versions_2(c1, c2) VALUES (2, 2);
UPSERT /*+ _l_ts_(2003)  */ INTO t_test_versions_2(c1, c2) VALUES (2, 3);
UPSERT /*+ _l_ts_(2004)  */ INTO t_test_versions_2(c1, c2) VALUES (2, 4);
UPSERT /*+ _l_ts_(2005)  */ INTO t_test_versions_2(c1, c2) VALUES (2, 5);
UPSERT /*+ _l_ts_(2006)  */ INTO t_test_versions_2(c1, c2) VALUES (2, 6);

Query only hot storage

Use _l_hot_only_ or its equivalent _l_hot_only_(true) to limit the scan to hot storage. Both forms return the same result:

SELECT /*+ _l_hot_only_ */ * FROM t_test_versions_2 WHERE c1 IN (1, 2, 3);
SELECT /*+ _l_hot_only_(true) */ * FROM t_test_versions_2 WHERE c1 IN (1, 2, 3);

Expected output:

+----+----+------+
| c1 | c2 | c3   |
+----+----+------+
| 1  | 1  | 22   |
| 2  | 6  | null |
+----+----+------+

Query all data

Use _l_hot_only_(false) to explicitly scan both hot and cold storage. This produces the same result as running the query without any hint:

-- Both statements return the same result.
SELECT /*+ _l_hot_only_(false) */ * FROM t_test_versions_2 WHERE c1 IN (1, 2, 3);
SELECT * FROM t_test_versions_2 WHERE c1 IN (1, 2, 3);

Expected output:

+----+----+------+
| c1 | c2 | c3   |
+----+----+------+
| 1  | 1  | 22   |
| 2  | 6  | null |
+----+----+------+