NO RANGE? UNKNOWN? What's the difference?

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

I was recently asked about NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN partitions for the RANGE_N function.  While these have been available since TD V2R5.0, there is sometimes confusion about what they mean and when to use or not use them.

UNKNOWN option is often thought to mean the partition that hold rows where the partitioning column is null.  This is usually the case since the expression x in a partitioning expression RANGE_N(x BETWEEN ..., UNKNOWN) is usually just simply the partitioning column.  But the actual definition is if the expression x in the RANGE_N results in null (which can occur or not depending on how the expression x is written even if the partitioning column is null or not), the RANGE_N function results in the number associated with UNKNOWN and that number is used as the partition number for the row (assuming the RANGE_N function is being used as a partitioning expression).  If x results in null and neither UNKNOWN nor NO RANGE OR UNKNOWN is specified, the RANGE_N function results in null.  Note the distinction between the value of a partitioning column, the result of the expression x, and the result of the RANGE_N function (while related, they are not necessarily the same).  There is no need to have an UNKNOWN partition (and it is recommended not to have one) if the expression x can never result in null or, if it is null for a row, the row should not be inserted.  The expresssion x is referred to as the test expression or test value for the RANGE_N function.

NO RANGE option is for rows where x is not null and is outside of any of the specified ranges.  There is no need to have this option if the specified ranges cover all nonnull values of x or if rows where x is outside the ranges should not be inserted.  If neither NO RANGE nor NO RANGE OR UNKNOWN is specified and x is not null but is outside any of the specified ranges, the RANGE_N function results in null.

NO RANGE OR UNKNOWN option is for combining NO RANGE and UNKNOWN rows into one partition (that is, x results in null or is outside of any of the specified ranges).  Again, if x can never result in null, use NO RANGE instead (or omit this also if NO RANGE is not needed as mentioned above).  This option cannot be specified if either one or both of the above two options are specified.

If neither NO RANGE nor NO RANGE OR UNKNOWN is specified and the resulting value of expression x does not have a range in which to go, the RANGE_N function results in null.

If neither UNKNOWN nor NO RANGE OR UNKNOWN is specified and the expression x results in null, the RANGE_N function results in null.

If a RANGE_N function being used as a partitioning expression results in null for a row, the row is rejected.

A row can also be rejected if a computation error such as divide by zero, conversion error, overflow, etc. occurs while evaluating the partitioning expression or, in the case of RANGE_N function, expression x.  Note that data type violations (for example, BYTE data type is used when BYTE data is not allowed) should be detected at table creation time.  There is not a RANGE_N option to specify a partition for these rows (this can only be done be coding the expression x such that these could not occur).