PolarDB:ALTER TABLE ADD SUBPARTITION

This step creates a RANGE-LIST composite partitioned table named sales_data. The table is partitioned by RANGE on sale_date and subpartitioned by LIST on region.

-- Create a RANGE-LIST composite partitioned table
CREATE TABLE sales_data (
    sale_id    INT,
    region     VARCHAR2(20),
    sale_date  DATE
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region)
(
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION sp_2023_asia   VALUES ('Asia'),
        SUBPARTITION sp_2023_europe VALUES ('Europe')
    )
);

Before you add the subpartition, verify that the target first-level partition exists and check its current list of subpartitions.

-- Check that the first-level partition p_2023 exists
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_DATA';

-- Check the existing subpartitions under p_2023 to confirm that the new value 'Africa' does not exist
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'SALES_DATA' AND PARTITION_NAME = 'P_2023';

Run the ADD SUBPARTITION command to add a new subpartition with the value 'Africa' to the p_2023 partition.

ALTER TABLE sales_data MODIFY PARTITION p_2023 ADD SUBPARTITION sp_2023_africa VALUES('Africa');

Verify that the new subpartition exists and can accept data that matches its value definition.

-- Structure verification: Confirm that the new subpartition has been added
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'SALES_DATA' AND PARTITION_NAME = 'P_2023';
-- The query result should include sp_2023_africa

-- Data verification: Try to insert data into the new subpartition and verify
INSERT INTO sales_data VALUES (101, 'Africa', TO_DATE('2023-09-15', 'YYYY-MM-DD'));

SELECT COUNT(*) FROM sales_data SUBPARTITION (sp_2023_africa);
-- The query result should be 1

This step creates a RANGE-RANGE composite partitioned table named order_history. The table is partitioned by RANGE on order_date and then subpartitioned by RANGE on order_date.

-- Assume that a tablespace named archive_tbs already exists
-- CREATE TABLESPACE archive_tbs DATAFILE 'archive_tbs.dbf' SIZE 10M;

-- Create a RANGE-RANGE composite partitioned table
CREATE TABLE order_history (
    order_id   INT,
    order_date DATE
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY RANGE (order_date)
(
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION sp_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD'))
    )
);

Verify that the target first-level partition exists and check the boundaries of its current subpartitions to ensure the new boundary does not cause a conflict.

-- Check that the first-level partition p_2023 exists
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'ORDER_HISTORY';

-- Check the existing subpartitions and their boundaries under p_2023
SELECT SUBPARTITION_NAME, HIGH_VALUE FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'ORDER_HISTORY' AND PARTITION_NAME = 'P_2023';

Run the ADD SUBPARTITION command to add a new subpartition with an upper boundary of 2023-07-01 to the p_2023 partition.

ALTER TABLE order_history MODIFY PARTITION p_2023 
ADD SUBPARTITION sp_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'));

Verify that the new subpartition was created, and that you can insert data into it.

-- Structure verification: Confirm that the new subpartition has been added and check its tablespace
SELECT * FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'ORDER_HISTORY' AND SUBPARTITION_NAME = 'SP_2023_Q2';
-- The query result should show SP_2023_Q2 and ARCHIVE_TBS

-- Data verification: Try to insert data into the new subpartition and verify
INSERT INTO order_history VALUES (201, TO_DATE('2023-05-20', 'YYYY-MM-DD'));

SELECT COUNT(*) FROM order_history SUBPARTITION (sp_2023_q2);
-- The query result should be 1