Example: Modifying a Table to Not Have a Primary Index
The following example deletes all of the rows in the orders table, alters the table to not have a primary index, adds both column partitioning and row partitioning, and adds a secondary index.
DELETE orders ALL; ALTER TABLE orders MODIFY NO PRIMARY INDEX PARTITION BY (COLUMN ADD 5, RANGE_N(o_ordertsz BETWEEN TIMESTAMP '2003-01-01 00:00:00.000000+00:00' AND TIMESTAMP '2009-12-31 23:59:59.999999+00:00' EACH INTERVAL '1' MONTH) ); CREATE UNIQUE INDEX(o_orderkey) on orders;
This produces the following table definition for orders.
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_comment VARCHAR(79) ) PARTITION BY (COLUMN ADD 5, RANGE_N(o_ordertsz BETWEEN TIMESTAMP '2003-01-01 00:00:00.000000+00:00' AND TIMESTAMP '2009-12-31 23:59:59.999999+00:00' EACH INTERVAL '1' MONTH) ), UNIQUE INDEX(o_orderkey);
Example: Modifying a Primary AMP Index Table to Not Have a Primary Index (NoPI)
This example uses the following table definition:
CREATE TABLE Orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHAR(1) CASESPECIFIC, o_totalprice DECIMAL(13,2) NOT NULL, o_ordertsz TIMESTAMP(6) WITH TIME ZONE NOT NULL, o_comment VARCHAR(79) ) PRIMARY AMP INDEX (o_orderkey) PARTITION BY COLUMN UNIQUE INDEX (o_orderkey);
Execute the following ALTER TABLE statement:
ALTER TABLE Orders MODIFY NO PRIMARY INDEX;
The following table results:
CREATE TABLE Orders ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHAR(1) CASESPECIFIC, o_totalprice DECIMAL(13,2) NOT NULL, o_ordertsz TIMESTAMP(6) WITH TIME ZONE NOT NULL, o_comment VARCHAR(79) ) NO PRIMARY INDEX PARTITION BY COLUMN UNIQUE INDEX (o_orderkey);