17.05 - Example: Dropping 2 Columns from a Column Partition Which Also Drops the Partition - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
Release Date
January 2021
Content Type
Programming Reference
Publication ID
English (United States)

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