use alter table...drop subpartition to delete a subpartition and the data stored in it - PolarDB

Drops a subpartition and its data from a composite partitioned table. This operation permanently drops the subpartition's data. Before you run this statement, ensure the subpartition's data is backed up or is no longer needed.

Synopsis

ALTER TABLE table_name DROP SUBPARTITION subpartition_name;

Parameters

Parameter	Required	Description	Example
`table_name`	Yes	The name of the composite partitioned table that contains the subpartition to be deleted.	`customer_data`
`subpartition_name`	Yes	The name of the subpartition to be deleted.	`p_2022_asia`

Notes

You must be the table owner or a privileged user to run this command.
You cannot drop the last subpartition in a table.
DROP SUBPARTITION acquires a table-level exclusive lock AccessExclusiveLock. This lock blocks all Data Manipulation Language (DML) and most Data Definition Language (DDL) operations on the table. Run this statement during off-peak hours and allow sufficient time for it to complete to avoid blocking other critical operations on the table.

Examples

This example drops the subpartition for user data from the Asia region for the year 2022 to free up storage space. The customer_data table is a Composite Partitioned Table that is partitioned by registration year (Range Partitioning) and subpartitioned by region (List Subpartitioning).

Prepare the environment

This step creates a composite partitioned table named customer_data. The table is range-partitioned by registration year and then list-subpartitioned by user 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'));

Perform the pre-check

Before dropping the subpartition, back up its data and verify its structure.

-- Important: Before you delete 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 the command

Run the DROP SUBPARTITION command to drop the p_2022_asia subpartition.

ALTER TABLE customer_data DROP SUBPARTITION p_2022_asia;

Verify the result

Verify that the subpartition and its data have been dropped.

-- Structure verification: Confirm that the subpartition is deleted.
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 deleted.
SELECT COUNT(*) FROM customer_data WHERE customer_id = 101;
-- The query result is 0.

Update table statistics.
After dropping a subpartition, immediately update the table's statistics to ensure the query optimizer generates accurate execution plans.
```
ANALYZE CUSTOMER_DATA;
```

FAQ

Q1: Why do I get the ORA-00942: table or view does not exist error?

A: This error typically occurs for two reasons: 1) you lack ALTER privileges on the target table, or 2) the table or schema name is misspelled. Check your privilege settings and verify that the object names are correct.

Q2: Why does the ORA-14006: invalid partition name fault occur?
A: This error indicates that the specified subpartition name does not exist or is misspelled. You can query the ALL_TAB_SUBPARTITIONS view to verify the correct name of the target subpartition. Note that if the name is not enclosed in double quotation marks, the database typically converts it to uppercase for matching.

Q3: Why do I get the ORA-01031: insufficient privileges error?
A: This error occurs because you do not have ALTER privileges on the target table. Contact your database administrator to grant the necessary privileges.

Q4: Can data be recovered after running DROP SUBPARTITION?
A: No. DROP SUBPARTITION is a physical deletion that bypasses the Recycle Bin. The only way to recover the data is to restore it from a database backup created before the operation.

Related SQL statements

ALTER TABLE DROP PARTITION: Deletes a partition and all its subpartitions.
ALTER TABLE ADD SUBPARTITION: Adds a new subpartition to an existing partition.
ALTER TABLE TRUNCATE SUBPARTITION: Deletes the data in a subpartition but retains its definition.
ALTER TABLE MERGE SUBPARTITION: Merges two subpartitions into a new subpartition.
ALTER TABLE SPLIT SUBPARTITION: Splits one subpartition into two subpartitions.