Use hints to query hot data in a Lindorm wide table - Lindorm

This topic describes how to use hints in SQL statements for LindormTable to query hot data.

Applicable engines and versions

Hints are applicable only to LindormTable 2.3.1 and later versions.

Note

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.

Prerequisites

Cold storage is enabled for your Lindorm instance. For more information, see Enable Capacity storage.

Usage

Important

Hints cannot be used to query only cold data.

Execute the following statement to create a test table and insert data into the table:

-- 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 into the table.
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);

Use hints to query data only in hot storage

You can use hints in the SQL statement to configure the _l_hot_only_(true) attribute. The following two statements provide examples on how to use hints to query data only in hot storage:

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);

The following result is returned:

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

Use hints to query all data

You can configure the _l_hot_only_(false) attribute in the SQL statement to query all table data, including hot and cold data.

Important

Configuring the _l_hot_only_(false) attribute in the SQL statement produces the same result as executing the statement without hints.

-- Query all data, including hot and cold data.
SELECT /*+ _l_hot_only_(false) */ * FROM t_test_versions_2 WHERE c1 IN (1, 2, 3);

-- The preceding statement and the following statement return the same result.
SELECT * FROM t_test_versions_2 WHERE c1 IN (1, 2, 3);

The following result is returned:

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