ALTER TABLE Join Index Examples - Advanced SQL Engine - Teradata Database

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