tencent cloud

TDSQL Boundless

ドキュメントTDSQL BoundlessGeneral ReferenceSQL ReferenceSQL StatementsCREATE PARTITION POLICY

CREATE PARTITION POLICY

ダウンロード
フォーカスモード
フォントサイズ
最終更新日: 2026-06-02 20:31:42

Description

Explicitly create partition affinity policies. Affinity can be used to control data placement and the affinity relationships between data, ensuring that they adhere to preset policies during automatic scheduling. By using affinity policies, users can bind partitions from different tables to the same physical node, reducing distributed overhead. This is suitable for table groups with frequent join queries.

Permission Requirements

None.

Feature Limits

The data distribution policy is currently only supported for primary partition tables with HASH and KEY partitions.
A time-scheduled data distribution policy can be bound to RANGE/RANGE COLUMNS partition tables to achieve automatic archiving of cold data (EXPIRE, START_TIME, END_TIME).
A data distribution policy can be modified or deleted only if it is not bound to any data object.

Syntax

CREATE PARTITION POLICY [IF NOT EXISTS] partition_policy_name
                        [partition_clause] [opt_using_distribution_policy];
                        
partition_clause:
     PARTITION BY HASH (INT) PARTITIONS partition_num
   ｜PARTITION BY KEY COLUMNS columns_num PARTITIONS partition_num
   
opt_using_distribution_policy:      
     USING DISTRIBUTION POLICY distribution_policy_name

Parameter Description

Parameter
Required
Description
partition_policy_name
Required
Name of the policy for partition affinity.
partition_clause
Optional
Structure used to specify the policy for partition affinity.
If not specified, a non-partition-structured affinity policy will be created (used for binding regular tables).
If the parameter is specified, an affinity policy with a partitioned structure will be created (used for binding partitioned tables).
Where partition_num specifies the number of partitions, columns_num indicates the number of partition columns. The binding can succeed only when the attributes of the partition table match them.
opt_using_distribution_policy
Optional
Specifies whether the affinity policy is bound to a distributed policy. Please create the data distribution policy in advance. For details, consult a technical support engineer.

Examples

Create a non-partition-structured affinity policy.
tdsql [(none)]> CREATE PARTITION POLICY pp1;
Query OK, 0 rows affected (0.07 sec)


tdsql [test]> CREATE TABLE  tbl1(id INT) USING PARTITION POLICY pp1;
Query OK, 0 rows affected

tdsql [test]> CREATE TABLE  tbl2(id INT) USING PARTITION POLICY pp1;
Query OK, 0 rows affected
Under the above policy, tbl1 and tbl2 will be stored on the same node.
Create an affinity policy for a primary HASH 4-partition structure.
tdsql [(none)]> CREATE PARTITION POLICY pp2 PARTITION BY HASH(INT) PARTITIONS 4;  
Query OK, 0 rows affected (0.02 sec)
Create a non-partition-structured affinity policy (bound to a data distribution policy).
tdsql [(none)]> CREATE PARTITION POLICY pp3 USING DISTRIBUTION POLICY dp_1;  
Query OK, 0 rows affected (0.01 sec)
Create an affinity policy for a primary HASH 4-partition structure (bound to a data distribution policy).
tdsql [(none)]> CREATE PARTITION POLICY pp4  
    -> PARTITION BY HASH(INT)  
    -> PARTITIONS 4  
    -> USING DISTRIBUTION POLICY dp_2;  
Query OK, 0 rows affected (0.02 sec)
Create an affinity policy for a primary KEY partition structure with 2 partition columns and 4 partitions.
tdsql [(none)]> CREATE PARTITION POLICY pp2  
    -> PARTITION BY KEY COLUMNS 2  
    -> PARTITIONS 4;  
Query OK, 0 rows affected (0.02 sec)
Create a table bound to a policy for partitioned affinity.
# Create a policy for partitioned affinity (hash 4 partitions)
tdsql [test]> CREATE PARTITION policy pp2 PARTITION BY HASH(INT) PARTITIONS 4;
Query OK, 0 rows affected

# Create table.
tdsql [test]> CREATE TABLE  orders(id INT) PARTITION BY HASH(id) partitions 4 USING PARTITION policy pp2;
Query OK, 0 rows affected

tdsql [test]> CREATE TABLE  order_details(id INT, oid, detail TEXT) PARTITION BY HASH(oid) partitions 4 USING PARTITION POLICY pp2;
Query OK, 0 rows affected
Through the above SQL, the HASH partitions of the orders and order_details tables are bound by affinity, ensuring that partitions with identical HASH values remain on the same node during placement and migration scheduling.
前の記事へ: CREATE DATABASE次の記事へ: CREATE TABLE

ヘルプとサポート

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

フィードバック