Grouping Column-Partitioned Join Index Column Data - Advanced SQL Engine - Teradata Database

You can group column-partitioned join index column data either in the column specification list or in the partitioning for the index, but not both. The following syntax describes grouping column-partitioned data in the column list for the index.

(column_name)

A list of columns to be grouped together into a column partition. You can only specify this option for a column-partitioned join index.

• If you precede the column grouping with the COLUMN keyword, Vantage stores column partitions using COLUMN format.
• If you precede the column grouping with the ROW keyword, Vantage stores the grouped columns using ROW format.
• If you specify neither COLUMN nor ROW, Vantage makes the determination of whether to stored the grouped columns using COLUMN or ROW format.

database_name.table_name

user_name.table_name

The fully qualified path to the column_name or the repeating column_name list for a compressed join index definition, if required for unique identification of column_name or ROWID.

COLUMN

The columns grouped in the following (column_name ) specification are to be stored using COLUMN format.

If you do not specify either COLUMN or ROW, Vantage determines which storage format to use.

You can also specify this option in the PARTITION BY clause, but you cannot specify the COLUMN or ROW options in both the select list and in the PARTITION BY clause of the same join index.

ROW

The columns grouped in the following (column_name ) specification are to be stored using ROW format.

You can also specify this option in the PARTITION BY clause, but you cannot specify the COLUMN or ROW options in both the select list and in the PARTITION BY clause of the same join index.

If you do not specify either ROW or COLUMN, Vantage determines which storage format to use.

column_alias

AS column_alias

An alias for column_name. This is mandatory for an expression.

• If the join index has a primary index, an alias is optional for the system-derived ROWID column.
• If the join index is column-partitioned, an alias is mandatory for the system-derived ROWID column.

All expressions specified in the select list must be aliased.

The AS keyword preceding column_name_alias is optional.

Do not use the name CountStar if you create an aggregate join index because the system reserves that name for any expression being summed, counted, or extracted in the aggregate definition.

AUTO COMPRESS

Enable autocompression for a column partition of a column-partitioned join index. Vantage automatically determines and applies the best available compression method, if it can reduce the size of physical rows. AUTO COMPRESS is the default.

Vantage applies any user-specified compression specified for the index columns in their base table and, for column partitions with COLUMN format also applies row header compression.

For more information about autocompression, see CREATE TABLE in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 , and Teradata Vantage™ - Database Design, B035-1094.

NO AUTO COMPRESS

Disable autocompression for a column partition of a column-partitioned join index. You cannot specify this option for a primary-indexed join index.