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, Teradata Database stores column partitions using COLUMN format.
- If you precede the column grouping with the ROW keyword, Teradata Database stores the grouped columns using ROW format.
- If you specify neither COLUMN nor ROW, Teradata Database 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, Teradata Database 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, Teradata Database 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. You cannot specify this option for a primary-indexed join index.
- If you do not specify either AUTOCOMPRESS or NO AUTO COMPRESS, Teradata Database uses the AUTO COMPRESS or NO AUTO COMPRESS specified after COLUMN keyword that begins the column partitioning specification. Otherwise, the AutoCompressDefault cost profile constant value determines whether the column partition uses autocompression. For more information about the AutoCompressDefault cost value constant, see SQL Request and Transaction Processing, B035-1142.
- Teradata Database 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 SQL Request and Transaction Processing, B035-1142SQL Data Definition Language Detailed Topics
and Database Design, B035-1094Database Design.
- 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.