15.10 - Example: Changing Column Grouping for a Column-Partitioned Table Using CREATE TABLE … AS Syntax - 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

This request creates a column-partitioned table with columns c and d assigned to the same partition using the column list rather than the partitioning to group the columns.

     CREATE MULTISET TABLE t37 (
        a INTEGER, 
        b INTEGER, 
       (c INTEGER, 
        d INTEGER)) 
     PARTITION BY (RANGE_N(a BETWEEN 1 
                             AND    10 
                             EACH 1), 
                   COLUMN);

This results in the following table definition for t37.

     CREATE MULTISET TABLE t37 ,NO FALLBACK ,NO BEFORE JOURNAL , 
     NO AFTER JOURNAL ,CHECKSUM = DEFAULT ,DEFAULT MERGEBLOCKRATIO (
       a INTEGER,
       b INTEGER,
       c INTEGER,
       d INTEGER)
     NO PRIMARY INDEX
     PARTITION BY (RANGE_N(a BETWEEN 1  
                             AND    10  
                             EACH 1),
                   COLUMN(a,b));

You next create table t37a by copying the definition from table t37 without copying its data.

     CREATE TABLE t37a AS t37 
     WITH NO DATA;

This results in the following table definition for t37a. Note that table t37a has the same PARTITION BY clause and the same column grouping as table t37.

     CREATE TABLE t37a ,NO FALLBACK ,NO BEFORE JOURNAL , 
     NO AFTER JOURNAL ,CHECKSUM = DEFAULT ,DEFAULT MERGEBLOCKRATIO (
       a INTEGER,
       b INTEGER,
       c INTEGER,
       d INTEGER)
     NO PRIMARY INDEX
     PARTITION BY (RANGE_N(a BETWEEN 1  
                             AND    10  
                             EACH 1),
                   COLUMN(a,b));

You next create table t37b by copying the definition for table t37 , but modifying the grouping, which you specify in the table column list as follows.

     CREATE TABLE t37b (a, (b, c), d) AS t37 
     WITH NO DATA;

This results in a table definition for t37b as follows. Table t37b a PARTITION BY clause as does table t37 but the column grouping is as specified in the SQL table column list of the CREATE TABLE request for t37b.

     CREATE MULTISET TABLE t37b, NO FALLBACK, NO BEFORE JOURNAL , 
     NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO (
       a INTEGER,
       b INTEGER,
       c INTEGER,
       d INTEGER)
     NO PRIMARY INDEX
     PARTITION BY (RANGE_N(a BETWEEN 1  
                             AND    10  
                             EACH 1),
                   COLUMN(a,d) ADD 10);