17.05 - Example: Changing Column Grouping for a Column-Partitioned Table Using CREATE TABLE … AS Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

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. 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 and 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 has 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);