Example: CHARACTER Partitioning Defined Using a RANGE_N Function - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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' 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);