Example: Adding a Single-Column Partition to a Table - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

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