Partition management

There are plenty of ways to use SQL statements in order to modify partitioned tables—you can drop, add, merge, split, or redefine partitions with the ALTER TABLE statement. There are also ways to retrieve partitioned tables and partition information. We will see each of these in the following sections:

• RANGE and LIST partition management
• HASH and KEY partition management
• Partition maintenance
• Obtain partition information

RANGE and LIST partition management

Partition adding and dropping is handled in a similar way for the RANGE and LIST partition types. A table partitioned by RANGE or LIST partitioning can be dropped using the ALTER TABLE statement with the DROP PARTITION option available.

Make sure you have the DROP privilege before executing the ALTER TABLE ... DROP PARTITION statement. DROP PARTITION will delete all the data and also remove the partition from the table partition definition.

The following example illustrates the DROP PARTITION option with the ALTER TABLE statement...