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