Where do the rows go?
With row partitioning (for a PPI or column-partitioned table), the Teradata Database makes sure rows are placed in their appropriate partitions. When the row partitioning for the table is altered, rows may need to move from one partition to another so they are in their appropriate partitions according to the altered partitioning. The partitioning expression must be deterministic (always computes the same value for the same value of the partitioning column) to properly place and retrieve the row.
For instance if the table has
ALTER TABLE will move rows automatically from
NO RANGE to newly-added ranges as needed. Also, if a range is dropped, rows from that partition are moved to the
NO RANGE OR UNKNOWN partition or a newly-added range that covers the dropped range.
An error occurs if there is no place to put a row unless you specify
WITH DELETE or
WITH INSERT clause which says what to do with such a row.
This is also true if updating a partitioning column in a row -- the row is moved as needed (except in this case there is no
WITH DELETE/INSERT clause so, if there is no associated partition with the new value, an error will occur for the update). Depending on the new value set for the partitioning column this could entail moving the row to the
NO RANGE OR UNKNOWN,
UNKNOWN, or other range partition. If the test expression in the
RANGE_N partitioning for a row was
NULL and therefore the row was in the
NO RANGE OR UNKNOWN or
UNKNOWN partition (assuming one of these was defined for the
RANGE_N partitioning), and the partitioning column value was changed so the test expression was not
NULL, the row would be moved to a range partition or
NO RANGE partition.
Note that it is the result of the test expression in the
NULL or not
NULL, not the partitioning column being null or not null that determines that the partition should be
UNKNOWN. However, most often the test expression is just the partitioning column; in this case, if the partitioning column is defined as
NOT NULL, there is no reason to define an
NO RANGE OR UNKNOWN partition though you may need a
NO RANGE partition. In general, if the test expression can never be
NULL or is never supposed to be
NO RANGE OR UNKNOWN partition should not be defined for the
RANGE_N partitioning expression.
- If none of
NO RANGE OR UNKNOWN,
UNKNOWNare specified, out-of-range and null partitioning values are rejected.
NO RANGEis specified without
UNKNOWN, null partitioning values for the test expression are rejected.
UNKNOWNis specified without
NO RANGE, out-of-range values are rejected.