Table partitioning design practices for AP scenarios

-- Create a table named t1 with four HASH partitions. The partitioning key is column C2.
CREATE TABLE t1(c1 INT, c2 INT) PARTITION BY HASH(c2) PARTITIONS 4;

-- Specify c2 = 1 to query and trim partition p1
EXPLAIN SELECT * FROM t1 WHERE c2 = 1;

The execution plan output is as follows:
```sql
+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| ===============================================                                    |
| |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                    |
| -----------------------------------------------                                    |
| |0 |TABLE FULL SCAN|t1  |1       |3           |                                    |
| ===============================================                                    |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([t1.c1], [t1.c2]), filter([t1.c2 = 1]), rowset=16                     |
|       access([t1.c2], [t1.c1]), partitions(p1)                                     |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
|       range_key([t1.__pk_increment]), range(MIN ; MAX)always true                  |

+------------------------------------------------------------------------------------+

  -- Hash partitioning, evenly distributed by user_id
  CREATE TABLE customer (
  user_id BIGINT NOT NULL,
  login_time TIMESTAMP NOT NULL,
  customer_name VARCHAR(100) NOT NULL,
  phone_num BIGINT NOT NULL,
  city_name VARCHAR(50) NOT NULL,
  sex INT NOT NULL,
  id_number VARCHAR(18) NOT NULL,
  home_address VARCHAR(255) NOT NULL,
office_address VARCHAR(255) NOT NULL,
age INT NOT NULL
)

PARTITION BY HASH(user_id) PARTITIONS 128;

### RANGE/RANGE COLUMNS partitioning
This method is suitable when the partitioning column can be easily divided into clear ranges. For example, a large table storing transaction records can be RANGE-partitioned by a timestamp column to organize data by time.

**Use cases:**

- When data grows over time or within specific numerical ranges (e.g., `order_time`, `price`).

  - When you need to quickly prune historical data (e.g., query data from the last month).
  **Design considerations:**

- **Partition key selection:**

  - Choose a timestamp column or a continuous numeric column.

- Align partition boundaries with business query conditions (e.g., partition by day or month).

- **Recommended number of partitions:**
- Set partitions based on data growth, such as by month.
**Example scenario:**
    ```sql
    -- Create a system log table with monthly RANGE partitions based on log time, enabling fast queries and data archiving.
    CREATE TABLE system_logs (
    log_id BIGINT,
    log_date TIMESTAMP NOT NULL,
    log_level VARCHAR(10),
    source_system VARCHAR(50),
user_id BIGINT,
log_message TEXT,
client_ip VARCHAR(15)
    )
    -- Primary partition: Monthly RANGE partitioning, using dates to directly define partition boundaries.
    PARTITION BY RANGE COLUMNS(log_date) (
    PARTITION p_202001 VALUES LESS THAN ('2020-02-01'),
    PARTITION p_202002 VALUES LESS THAN ('2020-03-01'),
    PARTITION p_202003 VALUES LESS THAN ('2020-04-01'),
    PARTITION p_202004 VALUES LESS THAN ('2020-05-01'),
    PARTITION p_202005 VALUES LESS THAN ('2020-06-01'),
    PARTITION p_202006 VALUES LESS THAN ('2020-07-01'),
    PARTITION p_202007 VALUES LESS THAN ('2020-08-01'),
    PARTITION p_202008 VALUES LESS THAN ('2020-09-01'),
    PARTITION p_202009 VALUES LESS THAN ('2020-10-01'),
    PARTITION p_202010 VALUES LESS THAN ('2020-11-01'),
    PARTITION p_202011 VALUES LESS THAN ('2020-12-01'),
PARTITION p_202012 VALUES LESS THAN ('2021-01-01'),
-- Default partition handles records with future data or invalid time formats

PARTITION p_future VALUES LESS THAN (MAXVALUE)

);

### LIST/LIST COLUMNS partitioning

This method is suitable when you need to explicitly control how rows are mapped to specific partitions. The advantage is that it allows for precise partitioning of unordered or unrelated datasets, but the disadvantage is that it is challenging to perform partition pruning in range queries.

  **Use cases:**
  - For discrete fields such as region or channel type.

- When you need to quickly prune data based on fixed categories (e.g., query users in the East China region).

  **Design considerations:**

- **Partition key selection:**

- Choose a discrete column with a limited number of values (e.g., `region` field with values `['east', 'west', 'south', 'north']`).
- Ensure all possible values are covered to avoid missing data.
    - **Partition number limit:**
    - Configure the number of partitions based on business logic.
    **Example scenario:**
    ```sql
    CREATE TABLE orders_by_region (
    order_id BIGINT COMMENT 'Order unique identifier',
    region_code INT NOT NULL PRIMARY KEY  COMMENT 'Region code (1=north, 2=east, 3=south, 4=west)',
customer_id BIGINT COMMENT 'Customer ID',
order_time DATETIME COMMENT 'Order creation time',
product_category VARCHAR(50) COMMENT 'Product category',
    order_amount DECIMAL(18,2) COMMENT 'Order amount',
    payment_status VARCHAR(20) COMMENT 'Payment status (e.g., PAID, UNPAID)'
    )
    PARTITION BY LIST(region_code)  -- Integer partition key
    (
PARTITION p_north VALUES IN (1),  -- Region code 1 corresponds to north
PARTITION p_east VALUES IN (2),

PARTITION p_south VALUES IN (3),

PARTITION p_west VALUES IN (4),

PARTITION p_other VALUES IN (DEFAULT)  -- Default partition for unknown regions

);

    -- 4. Clean up data periodically. For example, after data expires, delete data in the 7 partitions.
    ALTER TABLE business_data DROP PARTITION p20231025, p20231026, p20231027, p20231028, p20231029, p20231030, p20231031;
    ```
    ```sql
-- 1. Create a partitioned table and set a dynamic partitioning strategy.
CREATE TABLE t1 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  event_time DATETIME NOT NULL,
  metric_value DECIMAL(10,2),
  PRIMARY KEY (id, event_time))
DYNAMIC_PARTITION_POLICY
(
  ENABLE = true,
TIME_UNIT = 'day',

PRECREATE_TIME = '7day',

EXPIRE_TIME = '30day'

)

PARTITION BY RANGE COLUMNS (event_time)(

PARTITION p20231025 VALUES LESS THAN ('2023-10-26'));

In addition to RANGE partitioning, you can also use other partitioning methods based on your business requirements.

For more information about dynamic partitioning, see [Dynamic partitioning](../500.data-distribution/200.dynamic-partition.md), [Create a dynamic partitioned table in MySQL-compatible mode](../../../700.reference/300.database-object-management/100.manage-object-of-mysql-mode/300.manage-partitions-of-mysql-mode/260.dynamic-partition-of-mysql-mode/200.create-a-dynamic-partition-table-of-mysql-mode.md), and [Create a dynamic partitioned table in Oracle-compatible mode](../../../700.reference/300.database-object-management/200.manage-object-of-oracle-mode/200.manage-partitions-of-oracle-mode/260.dynamic-partition-of-oracle-mode/200.create-a-dynamic-partition-table-of-oracle-mode.md)

### Data distribution
   Partitions can also be used as units for data distribution management. Generally, HASH partitioning is used to disperse data, and it has the following advantages:
   - It can effectively achieve uniform data distribution and supports precise partition pruning based on the partitioning key.
- For multiple tables that need to be joined, if they are all HASH partitioned by the same JOIN key and have the same number of partitions, and you use the Table Group feature of OceanBase Database, you can bind partitions with the same hash value to the same node group. This enables Partition-Wise Join during JOIN operations, avoiding data shuffling across nodes and significantly improving query performance.
   **Hash partitioning also has limitations:**
   1. Once the number of partitions is set for HASH partitioning, modifying it is a heavy operation that involves rewriting all data in the table. Therefore, the number of partitions is usually not changed after it is set.
     2. For range queries on the partitioning key, partition pruning is not supported, and all partitions need to be accessed, which may result in read amplification.
     To address the scalability and range query limitations of HASH partitioning, OceanBase Database supports automatic partition splitting for rowstore tables.

## Hybrid data maintenance and distribution management

We can also support both data maintenance and distribution management through subpartitioning. Typically, the approach is: **use primary partitions for data maintenance and subpartitions for data distribution.** Each requirement can be addressed using the corresponding supported methods.

### Typical manual partitioning management

* **Primary partitions:**
- Type selection: Use RANGE or LIST partitions to match high-frequency query conditions (e.g., time range, region)
    - Recommended number of partitions: Set a reasonable range based on the distribution of query conditions and data maintenance needs (e.g., partition by month and retain data for 12 months, or divide into 4 LIST partitions by region)
    * **Subpartitions:**
    - Type selection: Use HASH partitions to ensure data is evenly distributed
    - Recommended number of subpartitions:
    - If only one primary partition is used for writing, the number of subpartitions in that primary partition should meet the resource requirements for even data distribution
    - If multiple primary partitions can be used for writing, the number of subpartitions should be sufficient to meet the resource requirements for even data distribution across the number of primary partitions
    **Here are two scenarios: Range + Hash and List + Hash**
    #### Range + Hash
Use RANGE partitions for the primary partition and specify `order_date` to quickly filter out partitions that do not need to be scanned, allowing for efficient data maintenance through partition management operations. Use HASH partitions for subpartitions to distribute writes or reads for the current month across 8 partitions, avoiding hotspots.
```sql
CREATE TABLE orders (
  user_id BIGINT NOT NULL COMMENT 'User ID (subpartition key)',
  order_date DATE NOT NULL COMMENT 'Order date (primary partition key)',
  amount DECIMAL(10,2) NOT NULL COMMENT 'Order amount',
  status TINYINT NOT NULL COMMENT 'Status: 0-Canceled, 1-Pending Payment, 2-Paid, 3-Shipped, 4-Completed',
region_code CHAR(6) NOT NULL COMMENT 'Region code (first 2 digits for province code)',
product_id INT NOT NULL COMMENT 'Product ID',

payment_method VARCHAR(20) COMMENT 'Payment method',

created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Record creation time')

PARTITION BY RANGE COLUMNS(order_date)
SUBPARTITION BY HASH(user_id) SUBPARTITIONS 8
(
    PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
    PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
    ...
    PARTITION p202601 VALUES LESS THAN ('2026-02-01')
    );

-- Primary partition: LIST by province (31 provincial-level administrative regions)
CREATE TABLE social_insurance_records (
record_id BIGINT,
province_code INT NOT NULL,  -- Province code (for example, 11 for Beijing and 31 for Shanghai).
payment_date DATE NOT NULL,
user_id VARCHAR(32) NOT NULL,

amount DECIMAL(10,2)

) PARTITION BY LIST (province_code)  -- Partitioning by province code in a list partition
SUBPARTITION BY KEY(user_id) SUBPARTITIONS 16  -- Secondary HASH partition

(

PARTITION p_beijing VALUES IN (11),

PARTITION p_shanghai VALUES IN (31),

PARTITION p_tianjin VALUES IN (12),

...

PARTITION p_xizang VALUES IN (54)

);

### Typical automatic partitioning management

1. **Primary partitions:** Choose dynamic partitions and configure parameters such as fixed time-based partitioning, how far in advance to pre-create partitions, and how long to retain historical partitions.