ALTER TABLE MODIFY partitioning Examples - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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 table at3 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;