Example: Defining Partitions Based on the Value of a

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

In this example, the session collation is ASCII when submitting the CREATE TABLE statement, and the pad character is <space>. The example defines two partitions (numbered 1 and 2) based on the value of a:

  • The value of a is between 'a         ' (a followed by 9 spaces) and 'b         '.
  • The value of a is between 'b         ' and 'c         '.
       CREATE SET TABLE t2
          (a VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC,
           b INTEGER)
       PRIMARY INDEX (a)
       PARTITION BY CASE_N(a BETWEEN 'a' AND 'b', a BETWEEN 'b' AND 'c');

The following INSERT statement inserts a character string consisting of a single <tab> character between the 'b' and '1'.

   INSERT t2 ('b	1', 1);

The following INSERT statement inserts a character string consisting of a single <space> character between the 'b' and '1'.

   INSERT t2 ('b 1', 2);

The following SELECT statement shows the result of the INSERT statements. Since the <tab> character has a lower code point than the <space> character, the first string inserted maps to partition 1.

   SELECT PARTITION, a, b FROM t2 ORDER BY 1;
   *** Query completed. 2 rows found. 3 columns returned.
   *** Total elapsed time was 1 second.
   PARTITION  a          b
 -----------  ------ -----
           1  b 1        1   (string contains single <tab> character)
           2  b 1        2   (string contains single <space> character)