Use ALTER TABLE ... TRUNCATE PARTITION to delete all rows from one or more partitions or subpartitions without dropping the partition structure.
Syntax
ALTER TABLE table_name
TRUNCATE PARTITION {partition_name | subpartition_name} [, {partition_name | subpartition_name}] ...Note
When you truncate a partition that has subpartitions, the operation also deletes all rows from every subpartition of that partition.
Parameters
| Parameter | Description |
|---|---|
table_name | The name of the partitioned table. The name can be qualified with a pattern. |
partition_name | The name of the partition to delete. |
subpartition_name | The name of the subpartition to delete. |
Examples
Delete a partition and a subpartition in one statement
You can specify multiple partitions and subpartitions in a single statement. The following statement deletes partition q1_2012 and subpartition p5 in one operation:
ALTER TABLE sales_range_list TRUNCATE PARTITION q1_2012, p5;Delete a single subpartition
ALTER TABLE sales_range_list TRUNCATE PARTITION p5;