Example: Using MODIFY to Repartition a Table and Saving Resulting Nonvalid Rows in a Save Table - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Assume that the orders table defined in Table Definitions for Examples exists and the following save table has been created to handle rows that are no longer valid when you change the row partitioning for the orders table.

    CREATE TABLE old_orders (
      o_orderkey      INTEGER NOT NULL,
      o_custkey       INTEGER,
      o_orderstatus   CHARACTER(1) CASESPECIFIC,
      o_totalprice    DECIMAL(13,2) NOT NULL,
      o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
      o_orderpriority CHARACTER(21),
      o_clerk         CHARACTER(16),
      o_shippriority  INTEGER,
      o_comment       VARCHAR(79))
     UNIQUE PRIMARY INDEX (o_orderkey);

The following ALTER TABLE request is valid if there are 0 or more rows with o_orderdate between January 1, 1992 and December 31, 1992 (DATE '1992-01-01 AND DATE '1992-12-31').

The MODIFY option moves any rows having those values into the old_orders save table and then deleted from orders.

     ALTER TABLE orders 
     MODIFY
       DROP RANGE BETWEEN DATE '1992-01-01' 
                  AND     DATE '1992-12-31'
       ADD  RANGE BETWEEN DATE '1999-01-01' 
                  AND     DATE '2000-12-31'
                  EACH INTERVAL '1' MONTH
     WITH INSERT INTO old_orders;

Suppose orders had been defined as a column-partitioned table, orders_cp.

The following ALTER TABLE request is valid if there are 0 or more rows with o_orderdate between January 1, 1992 and December 31, 1992 (DATE '1992-01-01 AND DATE '1992-12-31').

The MODIFY option moves any rows having those values into the old_orders save table and then deleted from orders_cp.

     ALTER TABLE orders_cp 
     MODIFY
       DROP RANGE BETWEEN DATE '1992-01-01' 
                  AND     DATE '1992-12-31'
       ADD  RANGE BETWEEN DATE '1999-01-01' 
                  AND     DATE '2000-12-31'
                  EACH INTERVAL '1' MONTH
     WITH INSERT INTO old_orders;