16.20 - Example: Character Partitioning, Session Mode, and Case Specificity - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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