ADD ROW (column_name )

Add row storage format for the column_name or column_name group.


ADD ROW (column_name) alters the column partition containing the column to have ROW format, if it is not already defined with ROW format.


data type

Data type for the column.

column attributes

Specify attributes for the column.

AUTO COMPRESS

Autocompress data in the column.

NO AUTO COMPRESS

Do not autocompress data in the column.

Example: Adding a ROW-Formatted Column Partition to a Column-Partitioned Table

The following example adds a two-column row partition to the column-partitioned orders table with user-specified ROW format and no autocompression.

     ALTER TABLE orders 
     ADD ROW(o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) 
     NO AUTO COMPRESS;

This produces the following a table definition with the new columns set to NULL.

     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 ALL BUT((o_ o_orderstatus, o_ordersubstatus),
                                 (o_comment, o_comment_ext1,
                                   o_comment_ext2), ROW(o_ship_addr,
                                  o_bill_addr) NO AUTO COMPRESS)) ADD 3,
                   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: Adding a Two-Column Partition with ROW Format 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 to the table, with user-specified ROW format and autocompression by default:

ALTER TABLE Orders 
  ADD ROW(o_ship_addr VARCHAR(500), o_bill_addr VARCHAR(200)) AUTO COMPRESS;

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) )
  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) ),
  UNIQUE INDEX(o_orderkey);