This example is similar to Example: CHARACTER Partitioning Using a CASE_N Function, but specifies a partitioning expression based on a RANGE_N function instead of a CASE_N function and LIKE.
This definition is preferred to that of Example: CHARACTER Partitioning Using a CASE_N Function because static row partition elimination is not supported for CASE_N expressions that specify LIKE operators.
In this example, the character in position 2 of last_name is a SPACE, so 'B' goes in partition 2. If the character in position 2 of last_name collates to less than the SPACE character (for example, TAB), the system assigns the row to partition 1.
In Example: CHARACTER Partitioning Using a CASE_N Function, a match on the first character is all that is required for a row to be assigned to the corresponding partition. In that case, the row goes into partition 3, because the conditions for the first two partitions are both false: last_name < 'A' , last_name < 'B'.
CREATE TABLE accounts2 (
cust_id INTEGER,
last_name VARCHAR(30) NOT CASESPECIFIC,
first_name VARCHAR(30),
city VARCHAR(50)
) PRIMARY INDEX (cust_id)
PARTITION BY RANGE_N (
last_name BETWEEN 'A','B','C','D','E','F','G',
'H','I','J','K','L','M','N',
'O','P','Q','R','S','T','U',
'V','W','X','Y','Z'
AND 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ',
NO RANGE,
UNKNOWN
);
The partitioning in the first part of Example: CHARACTER Partitioning Using a CASE_N Function can be duplicated using the following RANGE_N partitioning expression using a CAST expression. However, this form of RANGE_N partitioning is not desirable because static row partition elimination is not available when there is a CAST on the RANGE_N test value.
PARTITION BY RANGE_N ( CAST (last_name AS CHARACTER(1)) BETWEEN 'A','B','C','D','E','F','G', 'H','I','J','K','L','M','N', 'O','P','Q','R','S','T','U', 'V','W','X','Y','Z' AND 'Z', NO RANGE, UNKNOWN );
To enable static partition elimination for this table, create the same table, but specify ranges suffixed by 29 nulls each. Because the column type is VARCHAR(30), each range specifies one character suffixed with the number of nulls required to fill the remaining 29 characters allotted for the column. If the column type is VARCHAR(x), suffix each character specification for the range with x-1 nulls.
The RANGE_N example uses hexadecimal character literals. If the session character set is Latin, 2 digits are required to represent each character (58 zeros). If the session character set is Unicode, 4 digits are to represent each character (116 zeros).
Specifying the partitioning expression in this way enables the Optimizer to use static partition elimination for requests made against the table, and by eliminating unnecessary partitions, the speed of the table scan can be significantly enhanced.
CREATE TABLE accounts2 (
cust_id INTEGER,
last_name VARCHAR(30) NOT CASESPECIFIC,
first_name VARCHAR(30),
city VARCHAR(50))
PRIMARY INDEX (cust_id)
PARTITION BY RANGE_N (
last_name BETWEEN
('A'||'0000000000000000000000000000000000000000000000000000000000'XC),
('B'||'0000000000000000000000000000000000000000000000000000000000'XC),
('C'||'0000000000000000000000000000000000000000000000000000000000'XC),
('D'||'0000000000000000000000000000000000000000000000000000000000'XC),
('E'||'0000000000000000000000000000000000000000000000000000000000'XC),
('F'||'0000000000000000000000000000000000000000000000000000000000'XC),
('G'||'0000000000000000000000000000000000000000000000000000000000'XC),
('H'||'0000000000000000000000000000000000000000000000000000000000'XC),
('I'||'0000000000000000000000000000000000000000000000000000000000'XC),
('J'||'0000000000000000000000000000000000000000000000000000000000'XC),
('K'||'0000000000000000000000000000000000000000000000000000000000'XC),
('L'||'0000000000000000000000000000000000000000000000000000000000'XC),
('M'||'0000000000000000000000000000000000000000000000000000000000'XC),
('N'||'0000000000000000000000000000000000000000000000000000000000'XC),
('O'||'0000000000000000000000000000000000000000000000000000000000'XC),
('P'||'0000000000000000000000000000000000000000000000000000000000'XC),
('Q'||'0000000000000000000000000000000000000000000000000000000000'XC),
('R'||'0000000000000000000000000000000000000000000000000000000000'XC),
('S'||'0000000000000000000000000000000000000000000000000000000000'XC),
('T'||'0000000000000000000000000000000000000000000000000000000000'XC),
('U'||'0000000000000000000000000000000000000000000000000000000000'XC),
('V'||'0000000000000000000000000000000000000000000000000000000000'XC),
('W'||'0000000000000000000000000000000000000000000000000000000000'XC),
('X'||'0000000000000000000000000000000000000000000000000000000000'XC),
('Y'||'0000000000000000000000000000000000000000000000000000000000'XC),
('Z'||'0000000000000000000000000000000000000000000000000000000000'XC),
AND
('{'||'0000000000000000000000000000000000000000000000000000000000'XC),
UNKNOWN
);