PolarDB:ALTER TABLE DROP SUBPARTITION

Create a composite partitioned table named customer_data, range-partitioned by registration year and list-subpartitioned by region.

-- Create a composite partitioned table
CREATE TABLE customer_data (
    customer_id  INT NOT NULL,
    region       VARCHAR2(10),
    reg_date     DATE
)
PARTITION BY RANGE (reg_date)
SUBPARTITION BY LIST (region)
(
    PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION p_2022_asia    VALUES ('Asia'),
        SUBPARTITION p_2022_europe  VALUES ('Europe')
    ),
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION p_2023_asia    VALUES ('Asia'),
        SUBPARTITION p_2023_europe  VALUES ('Europe')
    )
);

-- Insert data
INSERT INTO customer_data VALUES (101, 'Asia', TO_DATE('2022-06-10', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (102, 'Europe', TO_DATE('2022-07-20', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (103, 'Asia', TO_DATE('2023-06-10', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (104, 'Europe', TO_DATE('2023-07-20', 'YYYY-MM-DD'));

Back up the subpartition data and verify the subpartition exists.

-- Important: Before you drop the subpartition, make sure that you have backed up the relevant data.

-- Check the subpartition structure to confirm that the p_2022_asia subpartition exists.
SELECT SUBPARTITION_NAME 
FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_DATA';

Run DROP SUBPARTITION to drop p_2022_asia.

ALTER TABLE customer_data DROP SUBPARTITION p_2022_asia;

1. Verify that the subpartition and its data have been dropped.

   -- Structure verification: Confirm that the subpartition is dropped.
   SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
   WHERE TABLE_NAME = 'CUSTOMER_DATA';
   -- The query result no longer contains p_2022_asia.
   
   -- Data verification: Confirm that the data in the subpartition is dropped.
   SELECT COUNT(*) FROM customer_data WHERE customer_id = 101;
   -- The query result is 0.

2. Update table statistics.

   Update statistics immediately after dropping a subpartition so the query optimizer generates accurate execution plans.

   ANALYZE CUSTOMER_DATA;