tencent cloud

TDSQL Boundless

문서TDSQL BoundlessGeneral ReferenceSQL ReferenceBasic ElementsOptimizer HintsUSE_RANGE_CACHE

USE_RANGE_CACHE

Download
포커스 모드
폰트 크기
마지막 업데이트 시간: 2026-07-13 16:59:29

Feature Description

USE_RANGE_CACHE and NO_RANGE_CACHE are a pair of statement-level hints for the query optimizer, controlling whether a single SQL statement uses Range Cache (range statistics cache) when it estimates the number of rows for a range scan.
Range Cache is a Range row quantity sampling cache maintained by the TDSQL Boundless kernel for SST data blocks in the storage engine. When estimating scan row quantities for Range scans, the optimizer can preferentially reuse the Range row quantities already sampled in this cache, thereby achieving more accurate cost estimation and more stable execution plans in scenarios with skewed data distribution or significant histogram statistical bias.
Whether to enable Range Cache is controlled by the global switch of the system variable range_stat_enable_cache. Beyond this global switch, you can temporarily adjust the behavior for a single SQL statement using the following two hints:
USE_RANGE_CACHE: Forces Range Cache to be enabled for this statement, even if the global switch range_stat_enable_cache is OFF.
NO_RANGE_CACHE: Forces Range Cache to be disabled for this statement, even if the global switch range_stat_enable_cache is ON.
Applicable Scenarios:
Range Cache is disabled globally, but you may want to enable it temporarily for specific SQL statements to verify or fix execution plans.
The estimation results from Range Cache do not meet expectations in certain scenarios. In such cases, you need to temporarily disable Range Cache for specific SQL statements and fall back to the default estimation path.
By using Statement Outline, you can inject hints into online SQL to adjust execution plans without modifying the business SQL.

Syntax

USE_RANGE_CACHE and NO_RANGE_CACHE are parameterless hints that apply to the entire statement and do not accept parameters such as table names, index names, or query block names.
/*+ USE_RANGE_CACHE */
/*+ NO_RANGE_CACHE */
Write the Hint directly after keywords such as SELECT, UPDATE, or DELETE in the statement. This is supported for SELECT, UPDATE, and DELETE statements.
Note:
Specifying both USE_RANGE_CACHE and NO_RANGE_CACHE (or specifying the same Hint repeatedly) in the same SQL statement triggers a conflict alarm ER_WARN_CONFLICTING_HINT, and the system uses the first-occurring Hint.

Use Limits

USE_RANGE_CACHE takes effect only under the following conditions. Otherwise, even if the Hint is specified, Range Cache is neither created nor used:
It takes effect only on user tables that use the RocksDB storage engine. Temporary tables and system tables do not participate in Range Cache by default.
The in-memory data size of the table does not exceed the threshold specified by range_stat_allowed_maximum_table_size.
The cluster edition where the instance resides supports the Range Cache feature. After you upgrade a cluster from a lower edition to the current edition, you must confirm that the cluster protocol version meets the requirements.
NO_RANGE_CACHE is not subject to the storage engine restrictions mentioned above and can disable the Range Cache estimation path for any SQL statement.

Examples

The following example demonstrates how to use USE_RANGE_CACHE and NO_RANGE_CACHE to control the Range Cache behavior for a single SQL statement in scenarios with large table data volumes, and verifies the cache usage through information_schema.RANGE_CACHE.
Prepare sample data:
CREATE TABLE range_cache_demo (
  id INT NOT NULL,
  f1 INT DEFAULT NULL,
  f2 INT DEFAULT NULL,
  f3 INT DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_f1 (f1),
  KEY idx_f2 (f2),
  KEY idx_f3 (f3, f2)
) ENGINE = ROCKSDB DEFAULT CHARSET = utf8mb4;
Example 1: Use NO_RANGE_CACHE to disable Range Cache
To disable Range Cache for a single SQL statement when the global switch range_stat_enable_cache = ON:
SELECT /*+ NO_RANGE_CACHE */ COUNT(1)
  FROM range_cache_demo
 WHERE f1 = 10;
UPDATE and DELETE statements are also supported:
BEGIN;
UPDATE /*+ NO_RANGE_CACHE */ range_cache_demo
   SET f1 = 1
 WHERE f1 = 10;
ROLLBACK;
Example 2: Use USE_RANGE_CACHE to forcibly enable Range Cache
To forcibly enable Range Cache for a single SQL statement when the global switch is disabled:
SET GLOBAL range_stat_enable_cache = OFF;

SELECT /*+ USE_RANGE_CACHE */ COUNT(1)
  FROM range_cache_demo
 WHERE f1 = 10;

SET GLOBAL range_stat_enable_cache = ON;
Example 3: Enable optimizer_trace to view the details of Hint taking effect
SET optimizer_trace = 'enabled=on';
SET range_stat_enable_trace_cache = ON;

SELECT /*+ USE_RANGE_CACHE */ COUNT(1)
  FROM range_cache_demo
 WHERE f1 = 10;

SELECT * FROM information_schema.optimizer_trace\\G
In the optimizer_trace output, you can view the estimation details of Range Cache to confirm whether the Hint is adopted and the specific estimated number of rows.
Attention:
USE_RANGE_CACHE and NO_RANGE_CACHE only affect the cost calculation path during the row count estimation phase of a Range scan. They do not change the actual data scan operators, nor do they bypass normal execution processes such as permission checks and transaction visibility.
When a Hint conflicts with the actual condition of a table (for example, the table is not a RocksDB engine table, or the table size exceeds range_stat_allowed_maximum_table_size), USE_RANGE_CACHE is automatically disabled. The system then falls back to the default estimation path without causing execution errors.
Do not indiscriminately add Hints to all SQL statements. Use Range Cache-related Hints only when you have confirmed that the default estimation path generates an unreasonable execution plan and that a better plan can be obtained by verifying with a Hint.
이전 주제: CACHE_RESULT다음 주제: CURRENT_GLOBAL_TIMESTAMP

도움말 및 지원

문제 해결에 도움이 되었나요?

피드백