15.10 - Example: CHARACTER Partitioning Using a CASE_N Function - 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

The following example specifies character partitioning with a partitioning expression based on the CASE_N function. Because this example does not support static row partition elimination (see SQL Request and Transaction Processing, B035-1142) because it specifies LIKE operators in its CASE_N function, you might consider using the example that follows it instead.

     CREATE TABLE accounts (
       cust_id     INTEGER, 
       last_name   VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC, 
       first_name  VARCHAR(30), 
       city        VARCHAR(50))
     PRIMARY INDEX (cust_id)
     PARTITION BY CASE_N (last_name LIKE ‘A%’,
                          last_name LIKE ‘B%’,
                          last_name LIKE ‘C%’,
                          last_name LIKE ‘D%’,
                          last_name LIKE ‘E%’,
                          last_name LIKE ‘F%’,
                          last_name LIKE ‘G%’,
                          last_name LIKE ‘H%’,
                          last_name LIKE ‘I%’,
                          last_name LIKE ‘J%’,
                          last_name LIKE ‘K%’,
                          last_name LIKE ‘L%’,
                          last_name LIKE ‘M%’,
                          last_name LIKE ‘N%’,
                          last_name LIKE ‘O%’,
                          last_name LIKE ‘P%’,
                          last_name LIKE ‘Q%’,
                          last_name LIKE ‘R%’,
                          last_name LIKE ‘S%’,
                          last_name LIKE ‘T%’,
                          last_name LIKE ‘U%’,
                          last_name LIKE ‘V%’,
                          last_name LIKE ‘W%’,
                          last_name LIKE ‘X%’,
                          last_name LIKE ‘Y%’,
                          last_name LIKE ‘Z%’,
                          NO CASE,
                          UNKNOWN);

You should consider using a CREATE TABLE request like the following in place of one that specifies LIKE in its partitioning expression. Because it does not specify LIKE operators in its CASE_N function and it supports static row partition elimination, thus enhancing the performance of requests made on the table.

     CREATE TABLE accounts (
       cust_id     INTEGER, 
       last_name   VARCHAR(30) CHARACTER SET UNICODE NOT CASESPECIFIC, 
       first_name  VARCHAR(30), 
       city        VARCHAR(50))
     PRIMARY INDEX (cust_id)
     PARTITION BY  CASE_N (last_name < 'A', last_name < 'B',
                           last_name < 'C', last_name < 'D',
                           last_name < 'E', last_name < 'F',
                           last_name < 'G', last_name < 'H',
                           last_name < 'I', last_name < 'J',
                           last_name < 'K', last_name < 'L',
                           last_name < 'M', last_name < 'N',
                           last_name < 'O', last_name < 'P',
                           last_name < 'Q', last_name < 'R',
                           last_name < 'S', last_name < 'T',
                           last_name < 'U', last_name < 'V',
                           last_name < 'W', last_name < 'X',
                           last_name < 'Y', last_name >= 'Z',
                           UNKNOWN);