ALTER TABLE MODIFY partitioning Examples - Teradata Vantage - Analytics Database

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;

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;

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 of the following ALTER TABLE statements, submitted in sequence, or the third ALTER TABLE statement, can make this modification to at3.

ALTER TABLE at3 ADD c INTEGER;
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

CREATE TABLE pi6 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;

ALTER TABLE pi6 MODIFY NOT PARTITIONED;

ALTER TABLE pi6 MODIFY PRIMARY INDEX NOT PARTITIONED;

ALTER TABLE pi6 MODIFY PRIMARY INDEX(a) NOT PARTITIONED;

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

CREATE TABLE p7 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;

ALTER TABLE p7 MODIFY NO PRIMARY INDEX NOT PARTITIONED;

CREATE TABLE p7 (a INTEGER, b INTEGER, c CHAR(10)) NO PRIMARY INDEX;