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

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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;