You cannot drop a partition in Adaptive Server 15.0. To remove a partition, unpartition the table, then repartition it using the same partitioning scheme as before, but with the undesired partition left out of the DDL statement. You can also truncate all of the data in a partition.
Removing a partition can be complicated. Often, the main reason to drop a partition is because you have archived the partitioned data. You can add or drop a partition to fix minor partitioning scheme problems without repartitioning the entire table. However, doing so can lead to a problem similar to the following.
Assume you created a range-partitioned table on the partition keys of 10,20,30,40,50, and so on, archived the data in the first partition (values less than or equal to 10), and then dropped the partition, leaving partition ranges of 20,30,40,50, and so on. Later, a user inputs valid data with a partition-key value of 5. Because of the mechanics of range partitioning, the newly inserted data is successfully added to the first partition (it is less than or equal to 20). However, problems occur if you must archive some of the data (or rebalance the partitioning as more data is added), and you add a new partition with the same partition key as the original (values less than or equal to 10). The previously inserted data (the value 5) may be left stranded as local indexes all point to the “new” first partition. The best solution to this problem is to relocate the 5 rows when you add the new partition, which means you are repartitioning the table instead of adding a partition as data is relocated.
When you drop a partition, you remove all data with it, which does not cause problems. However, problems may occur when you insert new data, as described in the following example, which results in more questions about data distribution than answers.
Assume you have a hash partition on an integer column using 10 partitions. Dropping one of the partitions removes the hash bucket for 1/10th of the possible data values. Also, assume that the particular hash bucket you removed held the hash keys for integers (5,32,41,and so on). If a user inserts a value of 32, consider whether the hashing algorithm changes to reflect the full domain across the remaining 9 partitions. If so, also consider whether the purpose of dropping the partition is to redistribute the data or remove it, which means that this is actually a repartition. Perhaps, instead, the value should be rejected as the hash bucket no longer exists, which is similar to inserting an unlisted value in a list partition.
Dropping a partition can be more complex than removing a partition and all its data. Sybase opted not to include this functionality in the initial release of Adaptive Server 15.0, but may introduce this capability in a later release. However, you can repartition a table and truncate a partition, which effectively covers nonrepartitioning considerations.
