Modify partition-key columns with care. These rules apply:
You cannot drop a column that is part of the partition key. You can drop columns that are not part of the partition key.
If you change the datatype of a column that is part of the partition key for a range-partitioned table, the bound of that partition is converted to the new datatype, with these exceptions:
Explicit conversions
Implicit conversions that result in data loss
Conversions from nonbinary datatypes to binary datatypes
See “Restrictions on partition keys and bound values for range-partitioned tables” for more information and examples of unsupported conversions.
In certain cases, if you modify the datatype of a partition-key column or columns, data may redistribute among the partitions:
For range partitions – if some partition-key values are close to the partition bounds, a datatype conversion may cause those rows to migrate to another partition.
For example, suppose the original datatype of the partition
key is float, and it is converted to integer.
The partition bounds are: p1 values <= (5), p2
values <= (10). A row with a partition
key of 5.5 is converted to 5, and the row migrates from p2 to p1.
For range partitions – if the sort order changes because the partition-key datatype changes, all data rows are repartitioned according to the new sort order. For example, the sort order changes if the partition-key datatype changes from varchar to datetime.
alter table fails if you attempt to alter the datatype of a partition-key column, and, after conversion, the new bound does not maintain the necessary ascending order, or not all rows fit in the new partitions.
See “Handling suspect partitions,” in Chapter 9, “Configuring Character Sets, Sort Orders, and Languages,” in the System Administration Guide: Volume 1.
For hash partitions – both the data value and the storage size of the partition-key datatype are used to generate the hash value. As a consequence, changing the datatype of the hash partition key may result in data redistribution.
