15.10 - Example: Adding a Single-Column Partition to a Table - 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

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