16.20 - Example: Dropping a Column from a Column Partition Which Also Drops the Partition - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

This example drops a column from a column partition and, because it is the last column in the partition, also drops the partition.

     ALTER TABLE orders 
     DROP o_comment_ext1;

This results in the following table definition for orders. Note that the ADD option value for level 1 increases to 2.

     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_salesperson    VARCHAR(5),
       o_ordersubstatus CHARACTER(1) CASESPECIFIC,
       o_ship_addr      VARCHAR(500),
       o_bill_addr      VARCHAR(200),
       o_alt_ship_addr  VARCHAR(500),
       o_alt_bill_addr  VARCHAR(200),
       o_item_count     INTEGER )
     PARTITION BY (COLUMN ALL BUT ( (o_orderstatus, o_ordersubstatus),
                        ROW(o_ship_addr, o_bill_addr) NO AUTO COMPRESS,
                        COLUMN(o_alt_ship_addr, o_alt_bill_addr)) ADD 2,
                   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);