CREATE TABLE Statement | Teradata Vantage - Example: CHARACTER Partitioning Using a CASE_N Function - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

The following example specifies character partitioning with a partitioning expression based on the CASE_N function. This example does not support static row partition elimination because the CASE_N function includes LIKE operators. See Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

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

The example below supports static row partition elimination because the CASE_N function does not include LIKE operators, which enhances the performance of queries 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);