16.20 - Example: Dropping a Column from a Column Partition and Modifying it to become a Single-Column 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 modifies that column partition to become a single-column partition with system-determined COLUMN format and autocompression.

     ALTER TABLE orders 
     DROP o_comment;

This results in the following table definition.

     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_comment_ext1   VARCHAR(79),
       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 1,
                   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);