Example: Adding a Two-Column Partition to a Table
This example uses a table definition with more columns than the version of orders used in Example: Adding a Single-Column Partition to a Table. The system default is AUTO COMPRESS. The ALTER TABLE statements all add the same two-column partition, (o_ship_addr, o_bill_addr ) to the orders table.
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), o_salesperson VARCHAR(5), o_ordersubstatus CHARACTER(1) CASESPECIFIC, o_comment_ext1 VARCHAR(79), o_comment_ext2 VARCHAR(79)) PARTITION BY COLUMN AUTO COMPRESS ALL BUT (o_ordersubstatus NO AUTO COMPRESS, (o_comment_ext1, o_comment_ext2) NO AUTO COMPRESS), UNIQUE INDEX(o_orderkey);
The following ALTER TABLE requests all add the same two-column partition (o_ship_addr , o_bill_addr ) to the table with user-specified ROW format and autocompression.
ALTER TABLE orders ADD ROW(o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) AUTO COMPRESS;
ALTER TABLE orders ADD ROW(o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200));
ALTER TABLE orders ADD (o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200));
ALTER TABLE orders ADD (o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) AUTO COMPRESS;
ALTER TABLE orders ADD (o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) NO AUTO COMPRESS;
ALTER TABLE orders ADD ROW(o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) NO AUTO COMPRESS;
ALTER TABLE orders ADD o_bill_addr VARCHAR(200) INTO o_ship_addr, ADD ROW(o_ship_addr);
The resulting table has the following definition.
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), o_salesperson VARCHAR(5), o_ordersubstatus CHARACTER(1) CASESPECIFIC, o_comment_ext1 VARCHAR(79), o_comment_ext2 VARCHAR(79), o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200) ) PARTITION BY COLUMN AUTO COMPRESS ALL BUT (o_ordersubstatus NO AUTO COMPRESS, (o_comment_ext1, o_comment_ext2) NO AUTO COMPRESS, ROW(o_ship_addr, o_bill_addr)), UNIQUE INDEX(o_orderkey);
Example: Adding a Two-Column and a Single-Column Partition to a Table with a Primary AMP Index
Assume the following table definition, with autocompression as the default.
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);
The following ALTER TABLE statement adds a two-column partition and a single-column partition to the table, with user-specified COLUMN format for the first partition, system-determined COLUMN format for the second partition, and autocompression by default.
ALTER TABLE Orders
ADD COLUMN(o_alt_ship_addr VARCHAR(500), o_alt_bill_addr VARCHAR(200)),
ADD o_item_count INTEGER;
This results in a table definition as follows:
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),
o_salesperson VARCHAR(5),
o_ordersubstatus CHAR(1) CASESPECIFIC,
o_comment_ext1 VARCHAR(79),
o_comment_ext2 VARCHAR(79),
o_ship_addr VARCHAR(500),
o_bill_addr VARCHAR(200),
o_alt_ship_addr VARCHAR(500),
o_alt_bill_addr VARCHAR(200),
o_item_count INTEGER )
PRIMARY AMP INDEX (o_orderkey)
PARTITION BY (
COLUMN AUTO COMPRESS ALL BUT (
o_ordersubstatus NO AUTO COMPRESS,
(o_comment_ext1, o_comment_ext2) NO AUTO COMPRESS,
ROW(o_ship_addr, o_bill_addr) NO AUTO COMPRESS,
COLUMN(o_alt_ship_addr, o_alt_bill_addr) ),
UNIQUE INDEX (o_orderkey);