Row Placement in PPI tables

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 RANGE_N partitioning, 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, 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, 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 RANGE_N being 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 UNKNOWN or 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 NULL, an UNKNOWN or NO RANGE OR UNKNOWN partition should not be defined for the RANGE_N partitioning expression.

Finally:

• If none of NO RANGE OR UNKNOWN, NO RANGE and UNKNOWN are specified, out-of-range and null partitioning values are rejected.
• If NO RANGE is specified without UNKNOWN, null partitioning values for the test expression are rejected.
• If UNKNOWN is specified without NO RANGE, out-of-range values are rejected.

http://developer.teradata.com/blog/paulsinclair/2012/03/where-do-the-rows-go