- If you specify PARTITION BY, the system accepts the request and alters the table to have a partitioning with the specified new partitioning expressions as long as one of the following is true.
- The table is empty.
- The new partitioning expressions are defined by DROP RANGE[#Ln] and ADD RANGE[#Ln] options that do not drop ranges other than from the beginning or end of the ranges, with at least one remaining range, and then does not add ranges between the resulting beginning and end of the ranges.
In addition, 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 or has only been modified such that dropping the RANGE[OR UNKNOWN] or UNKNOWN partitions would not cause an internal partition number to be skipped in the sequence of internal partition numbers for the ranges.
- Vantage derives a new table-level partitioning CHECK constraint from a new set of partitioning expressions for a table or join index and replaces the previous partitioning CHECK constraint.
- When you alter the partitioning of a partitioned database object, Vantage derives a new partitioning CHECK constraint from the new set of partitioning expressions for each partitioned table and replaces the previous partitioning CHECK constraint. See Partitioning CHECK Constraints for Partitioned Tables With 2-Byte Partitioning and Partitioning CHECK Constraint for Partitioned Tables with 8-Byte Partitioning for more information about the partitioning CHECK constraints that Vantage derives for row-partitioned and column-partitioned tables and join indexes, respectively.
If an existing row violates the new partitioning expressions and you have not specified a WITH DELETE or WITH INSERT clause, then you cannot apply the new partitioning.
- If an existing row violates a new partitioning expression and you have not specified a WITH DELETE or WITH INSERT INTO save_table null partition handler clause, then you cannot apply the new partitioning expression.
Rows that violate the implied partitioning CHECK constraint, including those whose partitioning expression evaluates to null, are not allowed in the table.
The general rules for MODIFY and MODIFY PRIMARY also apply to the multilevel case with the following exception. See General Rules for the MODIFY PRIMARY Clause.