15.10 - Example: CHARACTER Partitioning Defined Using a RANGE_N Function - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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.

Note that in this example, the character in position 2 of last_name is a SPACE, so 'B' would go into 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 would go into partition 3, because the conditions for the first two partitions would both be 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);

You can enable static partition elimination for this table by creating the same table, but specifying ranges that are suffixed by 29 nulls each, because the column is typed as VARCHAR(30), so each range specifies one character suffixed with the number of nulls required to fill the remaining 29 characters allotted for the column. If the relevant column had been specified as VARCHAR(50), then you would need to suffix each character specification for the range with 49 nulls, and so on. 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);