PolarDB:ALTER TABLE ADD SUBPARTITION

Create a RANGE-LIST composite partitioned table named sales_data, 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 adding the subpartition, verify that the target first-level partition exists and check its current 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 'Africa' does not already exist
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'SALES_DATA' AND PARTITION_NAME = 'P_2023';

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');

Confirm that the new subpartition exists and can accept data matching its value definition.

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

-- Data verification: insert a row and confirm it is routed to the new subpartition
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 result should be 1

Create a RANGE-RANGE composite partitioned table named order_history, partitioned by RANGE on order_date and subpartitioned by RANGE on order_date.

-- Assumes 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 confirm the new boundary does not conflict.

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

-- Check 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';

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'));

Confirm that the new subpartition was created and can accept data.

-- Structure verification: confirm the new subpartition exists
SELECT * FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'ORDER_HISTORY' AND SUBPARTITION_NAME = 'SP_2023_Q2';
-- The result should show SP_2023_Q2

-- Data verification: insert a row and confirm it is routed to the new subpartition
INSERT INTO order_history VALUES (201, TO_DATE('2023-05-20', 'YYYY-MM-DD'));

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