如何重組表的分區結構 - PolarDB

REORGANIZE PARTITION用於對LIST或RANGE分區表的分區進行結構重組（如合并、拆分或修改分區），同時自動重新分配資料且不遺失資料。

效果展示

拆分分區

-- 將 p2019_2020 拆分為 2019 和 2020 兩個父分割
ALTER TABLE sales_data
REORGANIZE PARTITION p2019_2020 INTO (
    PARTITION p2019 VALUES LESS THAN (2020) (
        SUBPARTITION s2019_q1,
        SUBPARTITION s2019_q2,
        SUBPARTITION s2019_q3,
        SUBPARTITION s2019_q4
    ),
    PARTITION p2020 VALUES LESS THAN (2021) (
        SUBPARTITION s2020_q1,
        SUBPARTITION s2020_q2,
        SUBPARTITION s2020_q3,
        SUBPARTITION s2020_q4
    )
);

合并分區

-- 合并 p2021 和 p2022 分區
ALTER TABLE sales_data
REORGANIZE PARTITION p2021, p2022 INTO (
    PARTITION p2021_2022 VALUES LESS THAN (2023) (
        SUBPARTITION s2021_2022_q1,
        SUBPARTITION s2021_2022_q2,
        SUBPARTITION s2021_2022_q3,
        SUBPARTITION s2021_2022_q4
    )
);

合并分區時，需為連續相鄰的分區。

修改分區

-- 修改分區為季度劃分
ALTER TABLE logs
REORGANIZE PARTITION p0, p1 INTO (
    PARTITION q1 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION q2 VALUES LESS THAN (TO_DAYS('2023-07-01')),
    PARTITION q3 VALUES LESS THAN (TO_DAYS('2023-10-01')),
    PARTITION q4 VALUES LESS THAN (TO_DAYS('2024-01-01'))
);

文法

ALTER TABLE table_name
    REORGANIZE PARTITION partition_names INTO (partition_definitions)

partition_definitions: {list_partition | range_partition}

subpartition_definition: {list_subpartition | range_subpartition ｜ hash_subpartition | key_subpartition}

partition_definitions分區說明

list_partition

PARTITION partition_name VALUES IN (value[, value]...) 
(subpartition, ...)

range_partition

PARTITION partition_name VALUES LESS THAN (value[, value]...) 
(subpartition, ...)

subpartition_definition子分區說明

list_subpartition

SUBPARTITION [subpartition_name] VALUES IN (value[, value]...) 
[TABLESPACE tablespace_name]

range_subpartition

SUBPARTITION [subpartition_name] VALUES LESS THAN (value[, value]...) 
[TABLESPACE tablespace_name]

hash_subpartition/key_subpartition為：

SUBPARTITION [subpartition_name ]
[TABLESPACE tablespace_name]

參數	說明
table_name	表名
partition_names	需要合并或拆分的現有分區名列表，以英文逗號分隔。
partition_definitions	新分區定義列表，以英文逗號分隔。
partition_name	需要建立的分區名稱。說明分區名稱在所有分區和子分區中必須是唯一的，且必須遵循給物件識別碼命名的慣例。
subpartition_name	需要建立的子分區名稱。說明子分區名稱在所有分區和子分區中必須是唯一的，且必須遵循給物件識別碼命名的慣例。

樣本

資料準備

-- 建立按年分區 + 按季度雜湊子分區的銷售表
CREATE TABLE sales_data (
    order_id INT AUTO_INCREMENT,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)  -- 主鍵必須包含分區鍵
)
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH( QUARTER(order_date) )
SUBPARTITIONS 4 (  -- 每個父分割預設4個子分區
    PARTITION p2019_2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 插入測試資料
INSERT INTO sales_data (order_date, amount) VALUES
('2020-03-15', 100.00),  -- p2019_2020
('2021-06-20', 200.00),  -- p2021
('2022-09-10', 300.00),  -- p2022
('2023-12-25', 400.00);  -- p_future

拆分分區

將p2019_2020拆分為p2019和p2020兩個分區。

ALTER TABLE sales_data
REORGANIZE PARTITION p2019_2020 INTO (
    PARTITION p2019 VALUES LESS THAN (2020) (
        SUBPARTITION s2019_q1,
        SUBPARTITION s2019_q2,
        SUBPARTITION s2019_q3,
        SUBPARTITION s2019_q4
    ),
    PARTITION p2020 VALUES LESS THAN (2021) (
        SUBPARTITION s2020_q1,
        SUBPARTITION s2020_q2,
        SUBPARTITION s2020_q3,
        SUBPARTITION s2020_q4
    )
);

合并分區

將p2021和p2022合并為一個p2021_2022分區。

ALTER TABLE sales_data
REORGANIZE PARTITION p2021, p2022 INTO (
    PARTITION p2021_2022 VALUES LESS THAN (2023) (
        SUBPARTITION s2021_2022_q1,
        SUBPARTITION s2021_2022_q2,
        SUBPARTITION s2021_2022_q3,
        SUBPARTITION s2021_2022_q4
    )
);

修改分區

將p2019、p2020、p2021_2022和p_future四個分區修改為p2019、p2020_2021和p2022_future三個分區。

ALTER TABLE sales_data
REORGANIZE PARTITION p2019, p2020, p2021_2022, p_future INTO (
    PARTITION p2019 VALUES LESS THAN (2020) (
        SUBPARTITION s2019_q1,
        SUBPARTITION s2019_q2,
        SUBPARTITION s2019_q3,
        SUBPARTITION s2019_q4
    ),
    PARTITION p2020_2021 VALUES LESS THAN (2022) (
        SUBPARTITION s2020_2021_q1,
        SUBPARTITION s2020_2021_q2,
        SUBPARTITION s2020_2021_q3,
        SUBPARTITION s2020_2021_q4
    ),
    PARTITION p2022_future VALUES LESS THAN MAXVALUE (
        SUBPARTITION s2022_future_1,
        SUBPARTITION s2022_future_2,
        SUBPARTITION s2022_future_3,
        SUBPARTITION s2022_future_4
    )
);