tencent cloud

TDSQL Boundless

Release Notes
Product Introduction
Overview
Scenarios
Product Architecture
Instance Types
Compatibility Notes
Kernel Features
Kernel Overview
Kernel Version Release Notes
Functionality Features
Performance Features
Billing
Billing Overview
Purchase Method
Pricing Details
Renewal
Overdue Payments
Refund
Getting Started
Creating an Instance
Connect to Instances
User Guide
Data Migration
Data Subscription
Instance Management
Configuration Change
Parameter Configuration
Account Management
Security Group
Backup and Restoration
Database Auditing
Tag Management
Use Cases
Technical Evolution and Usage Practices of Online DDL
Lock Mechanism Analysis and Troubleshooting Practices
Data Intelligent Scheduling and Related Practices for Performance Optimization
TDSQL Boundless Selection Guide and Practical Tutorial
Developer Guide
Developer Guide (MySQL Compatibility Mode)
Developer Guide (HBase Compatibility Mode)
Performance Tuning
Performance Tuning Overview
SQL Tuning
DDL Tuning
Performance White Paper
Performance Overview
TPC-C Test
Sysbench Test
API Documentation
History
Introduction
API Category
Making API Requests
Instance APIs
Security Group APIs
Task APIs
Backup APIs
Rollback APIs
Parameter APIs
Database APIs
Data Types
Error Codes
General Reference
System Architecture
SQL Reference
Database Parameter Description
TPC-H benchmark data model reference
Error Code Information
Security and Compliance
FAQs
Agreements
Service Level Agreement
Terms of Service
Privacy Policy
Data Processing And Security Agreement
Contact Us
Glossary
문서TDSQL BoundlessGeneral ReferenceSQL ReferenceSQL StatementsCREATE PARTITION POLICY

CREATE PARTITION POLICY

PDF
포커스 모드
폰트 크기
마지막 업데이트 시간: 2026-02-10 11:25:50

Description

Explicitly create policies for partition affinity. Affinity can be used to control methods for data placement and affinity relationships between data, ensuring compliance with preset policies during automatic scheduling. Through affinity policies, users can bind partitions of different tables to the same physical node, reducing distributed overhead.

Permission Requirements

None.

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, and columns_num specifies the number of partition columns. Only when the partitioned table's attributes match these specifications can it be successfully bound.
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 with a single-level structure for hash partitioning of 4 partitions.
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 with a single-level structure for hash partitioning of 4 partitions (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 with single-level key partitioning, featuring 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

도움말 및 지원

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

피드백