Updated on 2024-05-20 GMT+08:00

DROP PARTITION

You can run this command to remove unnecessary partitions. You can delete a partition by specifying the partition name or partition value.

  • This command cannot be applied to hash partitions.
  • Running this command will invalidate the global index. You can use the UPDATE GLOBAL INDEX clause to update the global index synchronously or rebuild the global index.

You can run ALTER TABLE DROP PARTITION to delete any partition from a range partitioned table or list partitioned table.

For example, delete the partition date_202005 from the range partitioned table range_sales by specifying the partition name and update the global index.
ALTER TABLE range_sales DROP PARTITION date_202005 UPDATE GLOBAL INDEX;
Alternatively, delete the partition corresponding to the partition value '2020-05-08' in the range partitioned table range_sales. Global indexes become invalid after this command is executed because the UPDATE GLOBAL INDEX clause is not used.
ALTER TABLE range_sales DROP PARTITION FOR ('2020-05-08');
  • If a partitioned table has only one partition, the partition cannot be deleted by using the ALTER TABLE DROP PARTITION statement.
  • If the partitioned table is a hash partitioned table, partitions in the table cannot be deleted by using the ALTER TABLE DROP PARTITION statement.