15.10 - Example: Character Partitioning, Session Mode, and Case Specificity - 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

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, Teradata Database 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));