The PARTITION TIER clause is used to select the query range based on the current storage cache policy status of the leaf partition tablet in the local base table reference of a SELECT statement. This enables pruning query partitions by hot and cold domains, supporting queries that target only hot data, only cold data, only non-cold data, or only non-hot data.
In shared storage mode, OceanBase AI Database supports managing the hot and cold status of table or partition data through the Storage Cache Policy. The actual status is maintained at the granularity of leaf partition tablets. Previously, if a business wanted to query only cold data or hot data, it needed to refine and modify the SQL by adding WHERE filter conditions or explicitly specifying the PARTITION(...) partition list, which was costly. With the PARTITION TIER clause, you can directly trim the query scope based on the tablet's hot or cold status without modifying the SQL.
Note
The PARTITION TIER clause is supported in OceanBase AI Database starting from V4.6.3. This clause is an extended syntax for OceanBase AI Database.
Syntax
SELECT select_expr [, select_expr] ...
FROM table_name
PARTITION [(partition_name [, partition_name] ...)]
TIER ({HOT | COLD | NOT_COLD | NOT_HOT});
-- When PARTITION(...) and TIER(...) are used together, the candidate partitions are first expanded by PARTITION(...), and then filtered by TIER(...).
Parameters
Parameters |
Note |
|---|---|
| table_name | Target table name, which can be a partitioned table or a non-partitioned table. |
| partition_name | Partition name, which is an optional parameter. If specified, first sort by the existing PARTITION(...) Semantically expand explicit candidate partitions, then by TIER Specify the hot and cold attribute filter. The names of a partition and subpartition can appear in the same candidate partition list. |
| tier_value | Specifies the filter conditions for hot and cold attributes for the TIER clause. This is a required parameter. Valid values: HOT, COLD, NOT_COLD, or NOT_HOT. For more information, see the values section below. |
The TIER clause supports the following values:
HOT: Access only the leaf partitions whose current attribute isHOT.COLD: Access only the leaf partitions whose current attribute isCOLD.NOT_COLD: Access only leaf partitions whose current attribute is notCOLD. This includesHOTandAUTOpartitions.NOT_HOT: Access only leaf partitions whose current attribute is notHOT, includingCOLDandAUTO.
Description
This topic describes the detailed rules of the PARTITION TIER clause in terms of candidate partition scope, effective time point and consistency, null selection semantics, and index behavior.
Candidate partition range
PARTITION TIER(xxx): The candidate range includes all normal leaf partitions in the table schema.PARTITION(p0, p1) TIER(xxx): First expands explicit candidates based on the existingPARTITION(...)semantics, then filters by tier.- Non-partitioned tables: The unique tablet of the table is used as the candidate. When a match is found, the entire table is accessed; if no match is found, an empty result set is returned.
- Partitioned tables: The tablets corresponding to each partition are considered.
- Subpartitioned tables: The status is determined by the final leaf subpartition tablet. The status of a parent partition does not override that of its leaf subpartitions.
- Hidden partitions are not included in the candidate range.
Effective Time and Consistency
The hot and cold status is read when the resolver generates a plan and is expanded into a fixed list of partitions. After the plan generation is completed, the hot and cold status is not re-read during execution.
Physical plans with the TIER clause are not written to the plan cache. When the same SQL is executed again, a new plan is generated and reads from the latest state; Server-side PREPARE/EXECUTE follows the same rule.
Null selection semantics
If no tablet matches the specified TIER value, the current table reference is treated as a null relationship, rather than degenerating into a semantics of "no explicit partition restriction".
Index behavior
The resolver only enumerates leaf tablets for the user-referenced primary table schema and does not separately read the hot/cold status of index tables. The expanded partition list reuses the behavior of normal explicit partition selection.
Limitations and considerations
- The
PARTITION TIERclause is only supported when used with local base table references in aSELECTstatement. - This syntax introduces five non-reserved keywords:
TIER,HOT,COLD,NOT_COLD, andNOT_HOT. They can still be used as table names, column names, or aliases in regular identifier contexts.
Examples
This section provides common usage examples for the PARTITION TIER clause.
Query all hot partitions
SELECT *
FROM t PARTITION TIER(HOT);
Query all leaf partitions with the current attribute set to HOT in table t.
Query cold partitions in a specified partitioned table
SELECT *
FROM t PARTITION(p0, p1) TIER(COLD);
First, expand the candidate partitions using PARTITION(p0, p1), then filter out the leaf partitions whose current attribute is COLD.
Query non-cold partitions in a specified subpartition
SELECT *
FROM t PARTITION(sp00, sp01) TIER(NOT_COLD);
Query the leaf partitions in the specified subpartition whose current attribute is not COLD.
Specify both the partition and subpartition at the same time
SELECT *
FROM t PARTITION(p0, sp11) TIER(HOT);
The names of a partition and subpartition can appear in the same candidate partition list.
Usage with Table Aliases and Index Hints
SELECT a.*
FROM t PARTITION(p0, sp11) TIER(HOT) AS a
USE INDEX(idx_t);
The PARTITION TIER clause can be used together with table aliases (AS a) and index hints (USE INDEX).
Use different tiers for the same table
SELECT h.id, h.payload, c.payload
FROM t PARTITION TIER(NOT_HOT) AS h
JOIN t PARTITION TIER(COLD) AS c
ON h.id = c.id;
In self-join scenarios, different table references to the same table can use different tiers, each pruning the query scope based on their respective hot/cold attributes.
References
- SELECT: the general
SELECTsyntax. - CREATE TABLE: Sets the hot/cold cache policy at the table or partition level using
STORAGE_CACHE_POLICY. - default_storage_cache_policy : The default hot/cold cache policy parameter at the tenant level.
- V$OB_TABLET_LOCAL_CACHE : Views the current hot and cold cache status of a tablet.