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

This example adds a column into the single-column partition for o_orderstatus, modifying the partition to be a multicolumn partition with system-determined COLUMN format and autocompression.

     ALTER TABLE orders 
     ADD o_ordersubstatus CHAR(1) CASESPECIFIC INTO o_orderstatus;

The following equivalent request performs the same actions to alter the orders table.

     ALTER TABLE orders 
     ADD (o_ordersubstatus CHAR(1) CASESPECIFIC) INTO o_orderstatus;

Both requests alter the orders table the have the following definition with o_ordersubstatus set to NULL in each row of the 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)
       o_salesperson    VARCHAR(5)
       o_ordersubstatus CHARACTER(1) CASESPECIFIC)
     PARTITION BY (COLUMN ALL BUT ((o_orderstatus, o_ordersubstatus))
                     ADD 4,
                   RANGE_N(o_ordertsz BETWEEN TIMESTAMP
                                      '2003-01-01 00:00:00.000000+00:00'
                                      AND     TIMESTAMP 
                                      '2009-12-31 23:59:59.999999+00:00' 
                                      EACH INTERVAL '1' MONTH) ),
     UNIQUE INDEX(o_orderkey);