Example: Altering a Column Partition to ROW Format for a Join Index with a Primary AMP Index
Assume this 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), 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);
Create a join index as follows:
CREATE JOIN INDEX jOrders AS SELECT ROWID AS rw, o_custkey, o_salesperson, o_bill_addr FROM Orders PRIMARY AMP INDEX (o_custkey) PARTITION BY COLUMN NO AUTO COMPRESS ALL BUT (o_salesperson AUTO COMPRESS);
Alter the column partition to have ROW format:
ALTER TABLE jOrders ADD ROW(o_bill_addr);
This results in a join index definition as follows:
CREATE JOIN INDEX jOrders AS SELECT ROWID AS rw, o_custkey, o_salesperson, o_bill_addr FROM Orders PRIMARY AMP INDEX (o_custkey) PARTITION BY COLUMN NO AUTO COMPRESS ALL BUT (o_salesperson AUTO COMPRESS, ROW(o_bill_addr));
Altering a Column Partition to AUTO COMPRESS for a Join Index with a Primary AMP Index
Assume 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), 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);
You create a join index with a primary AMP index:
CREATE JOIN INDEX jOrders AS SELECT ROWID AS rw, o_custkey, o_salesperson, o_bill_addr FROM Orders PRIMARY AMP INDEX (o_custkey) PARTITION BY COLUMN NO AUTO COMPRESS;
The following ALTER TABLE statement adds autocompression for the column partition:
ALTER TABLE jOrders ADD (o_salesperson) AUTO COMPRESS;
Example: Modifying a Single-Column Join Index Partition to Have Autocompression
The example uses the following definition for a join index defined on an orders table as used in the previous example. The system default is AUTO COMPRESS. However, the create table request overrides the default to set the columns to NO AUTO COMPRESS.
CREATE JOIN INDEX j_orders AS SELECT ROWID AS rw, o_custkey, o_salesperson, o_bill_addr FROM orders PARTITION BY COLUMN NO AUTO COMPRESS;
Modify the single-column partition o_salesperson to have autocompression.
ALTER TABLE j_orders ADD (o_salesperson) AUTO COMPRESS;
This request alters the definition of j_orders by changing the single-column partition consisting of column o_salesperson to have autocompression.
CREATE JOIN INDEX j_orders AS SELECT ROWID AS rw, o_custkey, o_salesperson, o_bill_addr FROM orders PARTITION BY COLUMN NO AUTO COMPRESS ALL BUT (o_salesperson AUTO COMPRESS);
Now modify the o_bill_addr single-column partition to have ROW storage format.
ALTER TABLE j_orders ADD ROW(o_bill_addr);
This request changes the definition of j_orders as follows.
CREATE JOIN INDEX j_orders AS SELECT ROWID AS rw, o_custkey, o_salesperson, o_bill_addr FROM Orders PARTITION BY COLUMN NO AUTO COMPRESS ALL BUT (o_salesperson AUTO COMPRESS, ROW(o_bill_addr));