15.10 - MODIFY partitioning - 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

Change partitioning for a column-partitioned table or join index.

Modifies the partitioning for a primary-indexed table, but not the primary index.

You can only alter the partitioning of a table that contains rows by dropping or adding row partitions at the beginning or end of a RANGE_N partitioning expression.

Example: Removing the Row Partitioning from a Column-Partitioned Table

This example deletes the rows in orders and then alters the table to remove the row partitioning.

     DELETE orders ALL;
     ALTER TABLE orders 
     MODIFY PARTITION BY COLUMN ALL BUT ((o_orderstatus,
                                          o_ordersubstatus),
                                          ROW(o_ship_addr, o_bill_addr) 
                                NO AUTO COMPRESS) ADD 3;

This results in the following table definition. Note the increase in the ADD option value, where all excess partitions are now assigned to level 1. Because of this modification, you could theoretically add 65,524 column partitions to the table; however, the limit on the maximum number of columns for a table would be reached first.

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

Example: Adding and Dropping Columns in a Column-Partitioned Table

Suppose you create the following column-partitioned table at3.

     CREATE TABLE at3 (
       a INTEGER, 
       b INTEGER, 
       c INTEGER,
       d INTEGER,
       e INTEGER,
       f INTEGER) 
     PARTITION BY COLUMN ((a, b), f, (d, e), c);

After you create table at3 you decide to drop column c and replace it with a new column g.

Any of the following four equivalent ALTER TABLE statements can make this modification to at3.

     ALTER TABLE at3 DROP c, ADD g INTEGER INTO c;
     ALTER TABLE at3 ADD g INTEGER INTO c, DROP c;
     ALTER TABLE at3 DROP c, ADD g INTEGER;
     ALTER TABLE at3 ADD g INTEGER, DROP c;

Notice the use of the INTO clause in the first 2 examples above. When you specify an INTO clause for a column or a group of columns being added, the column set being added is added to the column partition that contains the column specified by the column name in the INTO clause. Both of these examples add the new column g into the partition that contains column c.

The preceding ALTER TABLE requests produce the following new definition for at3.

     CREATE TABLE at3 (
       a INTEGER, 
       b INTEGER, 
       d INTEGER,
       e INTEGER, 
       f INTEGER, 
       g INTEGER)
     PARTITION BY COLUMN ((a, b), (d, e), f, g);

After altering table at3 , you decide to drop the existing columns d and e and replace them with new columns i and h, which are both to be contained within the same column partition.

Any of the following six equivalent ALTER TABLE requests can make this modification to at3.

     ALTER TABLE at3 DROP d, DROP e, ADD h INTEGER INTO d, ADD i INTEGER INTO e; 
     ALTER TABLE at3 ADD h INTEGER INTO e, DROP d, DROP e, ADD i INTEGER INTO d;
     ALTER TABLE at3 ADD h INTEGER INTO d, DROP d, ADD i INTEGER INTO e, DROP e;
     ALTER TABLE at3 DROP d, DROP e, ADD (h INTEGER, i INTEGER) INTO e;
     ALTER TABLE at3 DROP d, DROP e, ADD (h INTEGER, i INTEGER);
     ALTER TABLE at3 ADD (h INTEGER, i INTEGER), DROP d, DROP e;

Notice the use of the INTO clause in the first four examples above.

The preceding ALTER TABLE statements produce the following new definition for at3. Columns d and e no longer exist in this definition and columns h and i have been added.

     CREATE TABLE at3 (
       a INTEGER, 
       b INTEGER, 
       f INTEGER, 
       g INTEGER,
       h INTEGER,
       i INTEGER)
     PARTITION BY COLUMN ((a, b), f, g, (h, i));

Suppose you want to alter tableat3 to add new columns c and k to the table. Either the first two ALTER TABLE statements below, submitted in sequence, or the third ALTER TABLE statement, can make this modification to at3.

     ALTER TABLE at3 ADD c INTEGER;

      and

     ALTER TABLE at3 ADD k INTEGER INTO c;

      or

     ALTER TABLE at3 ADD (c INTEGER, k INTEGER);

The preceding ALTER TABLE requests produce the following new definition for at3.

     CREATE TABLE at3 (
       a INTEGER, 
       b INTEGER,
       c INTEGER,
       f INTEGER, 
       g INTEGER
       h INTEGER, 
       i INTEGER,
       k INTEGER)
     PARTITION BY COLUMN ((a, b), f, g, (h, i), (c, k));

Example: Modify a Primary Index-Column Partitioned Table to a Nonpartitioned or Primary Index-Nonpartitioned Table

This example uses the following table definition:
CREATE TABLE pi6 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;
Execute any one of the following ALTER TABLE statements on the empty table:
ALTER TABLE pi6 MODIFY NOT PARTITIONED;
ALTER TABLE pi6 MODIFY PRIMARY INDEX NOT PARTITIONED;
ALTER TABLE pi6 MODIFY PRIMARY INDEX(a) NOT PARTITIONED;
The following table definition results:
CREATE TABLE pi6 (a INTEGER, b INTEGER, c CHAR(10)) PRIMARY INDEX (a);

Example: Modify a Primary Index-Column Partitioned Table to a No Primary Index-Nonpartitioned Table

This example uses the following table definition:
CREATE TABLE p7 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;
Execute the following ALTER TABLE statement on the empty table:
ALTER TABLE p7 MODIFY NO PRIMARY INDEX NOT PARTITIONED;
The following table definition results:
CREATE TABLE p7 (a INTEGER, b INTEGER, c CHAR(10)) NO PRIMARY INDEX;