17.10 - Example: Adding a Single-Column Partition to a Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

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

The system default is AUTO COMPRESS.

The example uses the following definition for an orders table.

     CREATE TABLE orders (
       o_orderkey    INTEGER NOT NULL,
       o_custkey     INTEGER,
       o_orderstatus CHARACTER(1) CASESPECIFIC,
       o_totalprice  DECIMAL(13,2) NOT NULL,
       o_ordertsz    TIMESTAMP(6) WITH TIME ZONE NOT NULL,
       o_comment     VARCHAR(79) )
     NO PRIMARY INDEX, 
     PARTITION BY COLUMN,
     UNIQUE INDEX(o_orderkey);

The following ALTER TABLE requests all add the same single-column, o_salesperson, which by default is also a single-column partition with system-determined COLUMN format and autocompression to orders.

     ALTER TABLE orders 
       ADD o_salesperson VARCHAR(5);
     ALTER TABLE orders 
       ADD (o_salesperson VARCHAR(5));
     ALTER TABLE orders 
       ADD SYSTEM(o_salesperson VARCHAR(5)) AUTO COMPRESS;
     ALTER TABLE orders 
       ADD SYSTEM(o_salesperson VARCHAR(5));
     ALTER TABLE orders 
       ADD (o_salesperson VARCHAR(5)) AUTO COMPRESS;

The resulting table has the following definition.

     CREATE TABLE orders (
       o_orderkey INTEGER NOT NULL,
       o_custkey INTEGER,
       o_orderstatus CHAR(1) CASESPECIFIC,
       o_totalprice DECIMAL(13,2) NOT NULL,
       o_ordertsz TIMESTAMP(6) WITH TIME ZONE NOT NULL,
       o_comment VARCHAR(79),
       o_salesperson VARCHAR(5) )
     PARTITION BY COLUMN AUTO COMPRESS,
     UNIQUE INDEX(o_orderkey);