tencent cloud

TDSQL Boundless

ドキュメントTDSQL BoundlessGeneral ReferenceSQL ReferenceBasic ElementsOptimizer HintsCACHE_RESULT

CACHE_RESULT

Download
フォーカスモード
フォントサイズ
最終更新日: 2026-07-13 16:57:43

Feature Description

CACHE_RESULT is a subquery execution policy supported by the SUBQUERY Hint. It marks the results of a correlated subquery as cacheable. Upon receiving this Hint, the optimizer builds a Partial Result Cache for the subquery based on the correlation columns. When the outer query invokes the subquery multiple times with the same correlation key, the cache is hit, and the result is returned directly. This avoids repeated computation and scanning of the inner table.
The global switch for subquery result caching is controlled by the system variable subquery_cache_enabled. Whether a subquery enters the cache is also influenced by the cost threshold subquery_cache_cost_threshold. Even if subquery_cache_enabled is OFF, the optimizer still enables caching for that subquery as long as the CACHE_RESULT Hint is explicitly specified in the SQL. The cache hit status can be observed through the status variables subquery_cache_hit and subquery_cache_miss.
Applicable Scenarios:
When the correlation columns of a correlated subquery have low cardinality, the outer query accesses the subquery multiple times with the same correlation key.
The subquery itself incurs a high execution cost (due to aggregation, complex filtering, and so on) and is invoked repeatedly.
Subquery result caching is not enabled globally. You can enable it temporarily for specific SQL statements only.

Syntax

CACHE_RESULT must be used as a policy parameter of the SUBQUERY Hint and cannot be used independently.
/*+ SUBQUERY([@query_block_name] CACHE_RESULT) */
Parameter description:
@query_block_name (Optional): The name of the query block where the target subquery resides. If @query_block_name is omitted, the Hint is inlined after the SELECT statement of the subquery and applies to that subquery itself. If @query_block_name is specified, you must name the query block using the QB_NAME Hint within the target subquery.
Two typical ways to write it:
Inline writing: Write the Hint directly after the SELECT keyword of the subquery.
Named query block writing: Specify the target subquery by using @query_block_name in the outer query.

Related Variables

Variable Name
Type
Default Value
Scope
Description
subquery_cache_enabled
System variable
OFF
Global,Session
Global switch for subquery result caching. After the CACHE_RESULT Hint is specified, the corresponding subquery still enables caching even if this switch is OFF.
subquery_cache_cost_threshold
System variable
4000
Global,Session
Cost threshold for enabling subquery caching. The subquery goes to the caching path only when the estimated cost is higher than this threshold.
subquery_cache_hit
Status Variables
-
Global,Session
Number of subquery result cache hits.
subquery_cache_miss
Status Variables
-
Global,Session
Number of subquery result cache misses.

Examples

The following example demonstrates how to enable caching for a single SQL statement using the CACHE_RESULT Hint when subquery result caching is disabled globally.
Prepare sample data:
CREATE TABLE t1 (a INT, b INT, c VARCHAR(32));
CREATE TABLE t2 (a INT, b INT);

INSERT INTO t1 VALUES
  (1, 10, 'row1'), (2, 20, 'row2'), (3, 30, 'row3'),
  (4, 40, 'row4'), (5, 50, 'row5');
INSERT INTO t2 VALUES
  (1, 100), (1, 110), (2, 200), (2, 210),
  (3, 300), (3, 310), (4, 400), (5, 500);

ANALYZE TABLE t1, t2;

SET subquery_cache_enabled = OFF;
FLUSH STATUS;
Example 1: Inline writing
EXPLAIN FORMAT=TREE
SELECT *
  FROM t1
 WHERE b > (SELECT /*+ SUBQUERY(CACHE_RESULT) */ AVG(b)
              FROM t2
             WHERE t2.a = t1.a);
In the execution plan, you can observe Partial result cache on keys(t1.a), which indicates that a cache has been built for the subquery based on the correlation column t1.a.
Example 2: Named query block writing
EXPLAIN FORMAT=TREE
SELECT /*+ SUBQUERY(@subq1 CACHE_RESULT) */ *
  FROM t1
 WHERE b > (SELECT /*+ QB_NAME(subq1) */ AVG(b)
              FROM t2
             WHERE t2.a = t1.a);
After execution, you can observe the cache hit status through status variables:
SHOW STATUS LIKE 'subquery_cache%';
Example output:
Variable_name           Value
subquery_cache_hit      <hit count>
subquery_cache_miss     <miss count>
Note:
CACHE_RESULT takes effect only for correlated subqueries. Non-correlated subqueries are processed according to other subquery execution policies, and the Hint does not build a result cache.
CACHE_RESULT cannot be used in conflict with mutually exclusive policies (such as MATERIALIZATION and INTOEXISTS) within the same SUBQUERY Hint. Otherwise, the Hint will not take effect.
前の記事へ: INDEX_FOR_GROUPBY次の記事へ: USE_RANGE_CACHE

ヘルプとサポート

この記事はお役に立ちましたか？

フィードバック