PolarDB:ALTER TABLE TRUNCATE SUBPARTITION

Synopsis

ALTER TABLE table_name 
  TRUNCATE SUBPARTITION subpartition_name 
  [{DROP|REUSE} STORAGE]

Parameters

Notes

• You must be the table owner or have the ALTER permission on the table.

• TRUNCATE 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.

• As a DDL operation, TRUNCATE SUBPARTITION implicitly commits the current transaction. After the command is executed, the deleted data cannot be recovered using ROLLBACK. Back up your data before you perform this operation.

• TRUNCATE SUBPARTITION is a metadata operation that includes file deletion. It can be completed in seconds, even for tables with hundreds of millions of records. The I/O, CPU, and memory overhead is extremely low.

Examples

This example shows how to delete data from the subpartition for user data from the Asia region for the year 2022. The example uses a customer table that is composite-partitioned by registration year (RANGE) and user region (LIST).

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

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

-- Important: Before you empty the subpartition, make sure 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';

-- Check the subpartition data to confirm that it contains data.
SELECT COUNT(*) FROM customer_data SUBPARTITION (p_2022_asia);

ALTER TABLE customer_data TRUNCATE SUBPARTITION p_2022_asia;

-- Verify the structure: Confirm that the subpartition definition still exists.
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_DATA';
-- The query result should include p_2022_asia.

-- Verify the data: Confirm that the data in the subpartition has been emptied.
SELECT COUNT(*) FROM customer_data SUBPARTITION (p_2022_asia);
-- The query result is 0.

FAQ

Q1: What is the difference between TRUNCATE SUBPARTITION and DROP SUBPARTITION?

TRUNCATE SUBPARTITION deletes the data from a subpartition but retains its definition. DROP SUBPARTITION deletes both the subpartition definition and its data.

Q2: Can data be recovered after TRUNCATE SUBPARTITION is executed?

No. TRUNCATE is a DDL operation that permanently deletes data. By default, it is not recorded in the transaction log. The data cannot be recovered using standard methods such as rollback or flashback.

Q3: What is the difference between TRUNCATE SUBPARTITION and DELETE FROM ... SUBPARTITION?

TRUNCATE SUBPARTITION is an efficient DDL operation that directly deletes data by releasing data blocks and cannot be rolled back. DELETE is a DML operation that deletes data row by row. It records logs, can be rolled back, and fires delete triggers.

Q4: Why do I get the error "ORA-02149: relation ... does not exist"?

This error occurs because the subpartition name does not exist or the case of the specified name does not match the actual name. Names are case-sensitive. To confirm the name, perform the pre-check.

Q5: Why do I get the error "ORA-01031: insufficient privileges"?

This error occurs because the user who runs the command does not have the ALTER permission on the target table. Contact your database administrator to grant the required permission.

Related SQL statements

• ALTER TABLE... DROP SUBPARTITION: Deletes a subpartition definition and the data it contains.

• ALTER TABLE TRUNCATE PARTITION: Deletes the data from a primary partition and all its subpartitions.

• ALTER TABLE ADD SUBPARTITION: Adds a new subpartition to an existing primary partition.

• ALTER TABLE SPLIT SUBPARTITION: Splits one subpartition into two subpartitions.