17.10 - Example: Character Partitioning, Session Mode, and Case Specificity - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

If you create the character multilevel partitioned primary index table in the following example in an ANSI mode session, the system treats the test value (j||'a') as CASESPECIFIC because character literals are case specific by default in ANSI mode sessions. Similarly, all range boundary comparisons in the partitioning expression are case sensitive.

Note that this is not an advisable practice because specifying the || concatenation operator in a partitioning expression where one or both sides of the concatenation involves a column reference eliminates the possibility of the system being able to use either static or dynamic partition elimination to enhance the performance of queries made against the table.

If you create the table in a Teradata mode session, Vantage treats the test value as NOT CASESPECIFIC, and range boundary comparisons in the partitioning expression are case blind.

     CREATE TABLE t1(
       i INTEGER,
       j CHARACTER(4) NOT CASESPECIFIC,
       k INTEGER)
     PRIMARY INDEX(i)
     PARTITION BY (RANGE_N(j || 'a' BETWEEN 'aaaa' AND 'bbbb',
                                            'cccc' AND 'dddd', 
                  'eeee' AND 'ffff', 
                  'gggg' AND 'hhhh', 
                  'iiii' AND 'jjjj', 
                  'kkkk' AND 'llll',
                  'mmmm' AND 'nnnn',
                  'oooo' AND 'pppp',
                  'qqqq' AND 'rrrr',
                  'ssss' AND 'tttt',
          NO RANGE),
                   RANGE_N(k BETWEEN 1 
                             AND    10 
                             EACH    1));