General Guidelines for RANGE_N and CASE_N Functions
The following rules apply when using the RANGE_N and CASE_N functions to create a partitioning expression.
- For a table or join index with 2-byte partitioning not already at its maximum combined partition number, the maximum number of partitions for the first level is increased to the largest value that does not cause the maximum combined partition number to exceed 65,535.
If there is at least one level with an explicit ADD clause, there is at least one level that consists solely of a RANGE_N function with BIGINT data type, or there is column-partitioning, this is repeated for each of the other levels, from the second level to the last.
- The partitioning expression is rewritten to be CAST to INTEGER data type if it does not solely consist of a RANGE_N function and it does not already have INTEGER or BIGINT data type. If it cannot be CAST to INTEGER or BIGINT data type, the system returns an error to the requestor.
- The number of defined partitions for a row partitioning level is the number of row partitions specified by the RANGE_N or CASE_N function used to define the row partitioning for the level or 65,535 if the level is not based on a RANGE_N or CASE_N function.
RANGE_N Functions
The following conditions are true if a new partitioning expression consists solely of a RANGE_N function.
- The number of ranges defined must be less than or equal to 65,535 for 2-byte partitioning or less than or equal to 9,223,372,036,854,775,807 for 8-byte partitioning. Otherwise, the system returns an error to the requestor.
For single-level partitioning, the total number of partitions defined for such a partitioning expression can be up to 65,535 for 2-byte partitioning and up to 9,223,372,036,854,775,807 for 8-byte partitioning if both the NO RANGE and UNKNOWN partitions are specified.
For a RANGE_N function with INTEGER data type, the number of partitions defined must be less than or equal to 65,535. Otherwise, the system returns an error to the requestor.
For a RANGE_N function with BIGINT data type, the number of partitions defined must be less than or equal to 9,223,372,036,854,775,807. Otherwise, the system returns an error to the requestor.
- A range is combined with the previous range if the following conditions are all true.
- The previous range is not specified with a range start of *.
- The previous range is a multiple of its EACH range size if specified. That is, the last expanded range is not smaller than specified in the EACH clause.
- The end of the previous range is just prior the start of this range. A timestamp range end value specifying any of 59.999999 truncated to the precision of the test value through 61.999999 seconds truncated to the precision of the test value, inclusive, is considered to be just prior to the start of the next minute.
- The size (ignoring leap seconds) of the range (or its EACH range size if specified) is the same for both ranges.
The combined range has an EACH clause which is the same as the EACH clause of the prior range or, if it does not have an EACH clause, the combined range has an EACH clause with a range size equal to the size of the prior range.
- For a partitioning expression for single-level partitioning defined using only a RANGE_N function with INTEGER data type, the total number of partitions defined must be less than or equal to 2,147,483,647.
If the number of partitions exceeds 2,147,483,647, the system returns an error to the requestor.
For 2-byte single-level partitioning, the RANGE_N function cannot define more than 65,533 range partitions. Otherwise, the partitioning would be 8-byte partitioning.
For 2-byte single-level partitioning, the total number of partitions defined can be as many as 65,535 if you also specify both the NO RANGE and UNKNOWN partitions.
- If a partitioning expression is defined using only a RANGE_N function with BIGINT data type, the number of ranges defined must be less than or equal to 9,223,372,036,854,775,805.
If the number of ranges exceeds 9,223,372,036,854,775,805, the system returns an error to the requestor.
For single-level partitioning, the total number of partitions defined for such a partitioning expression can be as many as 9,223,372,036,854,775,807 if you also specify both the NO RANGE and UNKNOWN partitions.
CASE_N Functions
The following conditions are true if a partitioning expression consists only of a CASE_N function.
- The number of partitions defined must be less than or equal to 2,147,483,647. Otherwise, the system returns an error to the requestor because the CASE_N function has INTEGER data type and 2,147,483,647 is the largest possible INTEGER value.
- The number of partitions defined must be less than or equal to 2,147,483,647 because values returned by the CASE_N function have INTEGER data type.
If the number of partitions exceeds 2,147,483,647, the system returns an error to the requestor.