Usage Recommendations For Partitioning
Basing Partitioning on a Numeric Column
This is the form that is the easiest to code, but is rarely the optimal choice.
If a table has a division_number column defined as an integer with values between 1 and 65535, the following CREATE TABLE fragment can be used to establish partitioning by the division number:
CREATE TABLE …
PRIMARY INDEX (invoice_number)
PARTITION BY division_number;
If the division_number column for an attempted INSERT or UPDATE request has a value of 0 or less, is null, or is greater than 65535, then the request aborts and the system returns an error to the requestor. The row is not inserted or updated.
This clause can also be used if division_number is defined with a CHARACTER data type, provided the values are always digits so that the column can be cast to INTEGER, because the system performs an implicit cast to the INTEGER type whenever necessary.
You can specify as many as 65,535 partitions if there are that many distinct values for the column. If you assume the more likely situation in which a company has, perhaps, four divisions, then there are only four partitions with rows. The table is not any larger because of the empty partitions, though the default Optimizer assumption that there are 65,535 partitions might sometimes mislead it into making suboptimal plan choices.
A disadvantage of this form is that the partitioning cannot be altered unless the table is empty.