Following are the rules and restrictions for modifying column-partitioned tables:
MODIFY NO PRIMARY
If you specify MODIFY NO PRIMARY, you must specify the NOT PARTITIONED option or a PARTITION BY clause.
You can also specify MODIFY NO PRIMARY for nonpartitioned NoPI tables, in addition to column-partitioned tables.
Specifying COLUMN partitioning in a PARTITION BY clause
You cannot specify MODIFY NO PRIMARY and NOT PARTITIONED on a table without a primary index or primary AMP index which is not partitioned.
If you alter a SET table to be a table with MODIFY NO PRIMARY, the system alters the table kind to be MULTISET by default. See Rules for Modifying Populated and Unpopulated Tables and Join Indexes.
- Table with a primary index or primary AMP index being modified to have no primary index
- NoPI or column-partitioned table being modified to have a primary index or primary AMP index.
- Partitioned table being modified to have no partitioning.
- Nonpartitioned table being modified to have partitioning.
- Partitioned table being modified to have a new partitioning using a PARTITION BY clause.
New Partitioning Using DROP and ADD RANGE Clauses
To modify a partitioned table to have a new partitioning, you must define the new partitioning expressions using DROP RANGE#L n and ADD RANGE#L n options that do not drop ranges other than from the beginning or end of the range sequence with at least one remaining range, and do not add ranges between the resulting beginning and end of the ranges.
If the table has multilevel partitioning and the modification of a partitioning expression includes dropping the NO RANGE [OR UNKNOWN] or UNKNOWN partitions, that partitioning expression must not have previously been modified.
The resulting number of partitions for a level must be between 1 and the maximum defined for that level.