ALTER TABLE ADD ROW (column_name) Examples - 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™

Example: Adding a ROW-Formatted Column Partition to a Column-Partitioned Table

The following example adds a two-column row partition to the column-partitioned orders table with user-specified ROW format and no autocompression.

ALTER TABLE orders 
ADD ROW(o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) 
NO AUTO COMPRESS;

This produces the following a table definition with the new columns set to NULL.

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,
  o_comment_ext1   VARCHAR(79),
  o_comment_ext2   VARCHAR(79),
  o_ship_addr      VARCHAR(500),
  o_bill_addr      VARCHAR(200) )
PARTITION BY (COLUMN ALL BUT((o_ o_orderstatus, o_ordersubstatus),
                            (o_comment, o_comment_ext1,
                             o_comment_ext2), ROW(o_ship_addr,
                             o_bill_addr) NO AUTO COMPRESS)) ADD 3,
              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);

Example: Adding a Two-Column Partition with ROW Format to a Table with a Primary AMP Index

Assume the following table definition, with autocompression as the default.

CREATE TABLE Orders
    ( o_orderkey INTEGER NOT NULL,
      o_custkey INTEGER,
      o_orderstatus CHAR(1) CASESPECIFIC,
      o_totalprice DECIMAL(13,2) NOT NULL,
      o_ordertsz TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      o_comment VARCHAR(79) )
  PRIMARY AMP INDEX (o_orderkey), PARTITION BY COLUMN,
  UNIQUE INDEX (o_orderkey);

The following ALTER TABLE statement adds a two-column partition to the table, with user-specified ROW format and autocompression by default:

ALTER TABLE Orders 
  ADD ROW(o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) AUTO COMPRESS;

This results in a table definition as follows:

CREATE TABLE Orders
    ( o_orderkey INTEGER NOT NULL,
      o_custkey INTEGER,
      o_orderstatus CHAR(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 CHAR(1) CASESPECIFIC,
      o_comment_ext1 VARCHAR(79),
      o_comment_ext2 VARCHAR(79),
      o_ship_addr VARCHAR(500),
      o_bill_addr VARCHAR(200) )
  PRIMARY AMP INDEX (o_orderkey)
  PARTITION BY (
    COLUMN AUTO COMPRESS ALL BUT (
                     o_ordersubstatus NO AUTO COMPRESS,
                     (o_comment_ext1, o_comment_ext2) NO AUTO COMPRESS,
                     ROW(o_ship_addr, o_bill_addr) ),
  UNIQUE INDEX(o_orderkey);