16.20 - Example: Dropping 2 Columns 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 two columns from a column partition, and because that leaves the partitions with no columns, also drops the column partition.

     ALTER TABLE orders 
     DROP o_alt_ship_addr, 
     DROP o_alt_bill_addr;

This results in a table definition as follows. note that ADD option value for level 1 increases to 3.

     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_item_count     INTEGER )
     PARTITION BY (COLUMN ALL BUT ( (o_orderstatus, o_ordersubstatus),
                                     ROW(o_ship_addr, o_bill_addr) 
                                     NO AUTO COMPRESS) ADD 3,
                   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);