15.10 - ADD (column_name ) AUTO COMPRESS - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Add autocompression for the column partition. You can add compression to a set of existing columns whether or not the table is empty or contains data. You can only specify autocompression for column-partitioned tables. 

You cannot modify the autocompression or partition storage for a column partition more than once in the same ALTER TABLE request.


The AutoCompressDefault cost profile constant can be set to change the autocompression default. For more information about the AutoCompressDefault cost profile constant, see SQL Request and Transaction Processing, B035-1142.



AUTO COMPRESS
Applies autocompression to the column or column partition. AUTO COMPRESS is the default. Teradata Database applies autocompression for a physical row on a per container basis. For efficiency, the system may use the autocompression method chosen for the previous container, including not using autocompression, if that is more effective.
NO AUTO COMPRESS
The column partition containing the column is not autocompressed.

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