16.20 - Table Definitions for Examples - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

This topic provides the table definitions for the following examples:

Example: Non-Valid MODIFY PRIMARY INDEX Statements through Example: Drop and Add Partition Ranges and Delete Rows Outside the Defined Ranges and Example: Using MODIFY to Repartition a Table and Saving Resulting Nonvalid Rows in a Save Table through Example: Revalidating the Partitioning for a Table use the following tables.

The orders table has a primary index on o_orderkey and single-level partitioning. The table also has a USI defined on o_orderkey.

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHARACTER(21),
       o_clerk         CHARACTER(16),
       o_shippriority  INTEGER,
       o_comment       VARCHAR(79))
     PRIMARY INDEX (o_orderkey)
     PARTITION BY RANGE_N(o_orderdate
                          BETWEEN DATE '1992-01-01' 
                          AND     DATE '1998-12-31' 
                          EACH INTERVAL '1' MONTH)
     UNIQUE INDEX (o_orderkey);

The orders_cp table is multilevel column-partitioned with row partitioning on the second partitioning level that is defined using the same partitioning expression as the only partitioning level of orders. The table also has a USI defined on o_orderkey.

     CREATE TABLE orders_cp (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHARACTER(21),
       o_clerk         CHARACTER(16),
       o_shippriority  INTEGER,
       o_comment       VARCHAR(79))
     NO PRIMARY INDEX 
     PARTITION BY (COLUMN,
                  RANGE_N(o_orderdate BETWEEN DATE '1992-01-01'
                                      AND     DATE '1998-12-31' 
                                      EACH INTERVAL '1' MONTH))
     UNIQUE INDEX (o_orderkey);