- You can only define standard base data tables as column-partitioned tables.You cannot define any of the following table types as column-partitioned tables.
- Global temporary tables
- Volatile tables
- Global temporary trace tables
- Queue tables You can specify NO QUEUE for a column-partitioned table
- Error tables
- You cannot create a column-partitioned table as a SET table.
The default and only valid table type for column-partitioned tables in all session modes is MULTISET. This means that if you do not specify an explicit table type of MULTISET, a column-partitioned table is always a MULTISET table by default.
- If you specify the COLUMN keyword for a partitioning level in a partitioning expression of a PARTITION BY clause, the table is column-partitioned.
You cannot specify COLUMN for more than one partitioning level in a table definition.
- A column-partitioned table can be assigned row-level security privileges.
Columns of a column-partitioned table can be defined as row-level security columns if they qualify as row-level security constraints. See Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100 and Teradata Vantage™ - SQL Data Control Language, B035-1149 for more information about row-level security constraints and privileges.
Autocompression is applicable to a column partition that contains 1 or more row-level security constraint columns.
- You can specify either AUTO COMPRESS or NO AUTO COMPRESS as the default for column partitions. If you do not explicitly specify either, Teradata Database uses AUTO COMPRESS.
- You can group partition columns in either the column list or in the PARTITION BY clause.
Grouping partition columns in the column list enables a simpler, but less flexible, specification of column groupings than grouping in the COLUMN specification of a PARTITION BY clause.
- Teradata Database derives a partitioning CHECK constraint from the partitioning level definitions for a column-partitioned table. For further information about partitioning CHECK constraints, see Partitioning CHECK Constraints for Partitioned Tables With 2-Byte Partitioning and Partitioning CHECK Constraint for Partitioned Tables With 8-Byte Partitioning.
The text for this partitioning constraint cannot exceed 16,000 characters.
- You cannot specify CHARACTER SET KANJI1 for a character data column in a column-partitioned table.
Instead, specify CHARACTER SET UNICODE.
- A CREATE TABLE … AS source_table statement copies the indexes and partitioning for the column-partitioned source table to the target table if you do not specify an index list for the target table.
If Teradata Database copies the partitioning and the source partitioning includes column partitioning, it copies the column partitioning definition as part of the partitioning with the following exception: if you specify grouping in the column list for the target table, Teradata Database uses the specified grouping for the column partitioning of the new table instead of the grouping defined for the source table.
- A CREATE ERROR TABLE statement for a column-partitioned table creates an error table associated with that table.
The system creates the error table the same as it would for a non-column-partitioned table except that the error table is created as a nonpartitioned NoPI table.
- You cannot create a hash index on a column-partitioned tables because hash indexes inherit the primary index of their underlying base table, and column-partitioned tables have no primary index.
- You cannot use MERGE statements to update the rows of a column-partitioned target table or to insert rows into a column-partitioned target table.MERGE statements can update or insert into a primary-indexed target table from a column-partitioned source table.
- If a table is partitioned, its fallback rows are partitioned identically to its primary data rows.
If the primary data rows have a primary index or primary AMP index, the fallback data rows have the same primary index or primary AMP index.
If the primary data rows do not have a primary index or primary AMP index, the fallback data rows also do not have a primary index or primary AMP index.
- You can define a large variety of partitioning expressions and column groupings for column partitioning with a large range in the number of combined partitions. You must consider the usefulness of defining a particular partitioning and its impact on performance and storage.
See Performance Issues for Column-Partitioned Tables for column partition-related performance considerations.
- The following table presents the rules for using the NO AUTO COMPRESS option.
| Option | Description |
|---|---|
| AUTO COMPRESS for a column partition | applies autocompression for physical rows if it finds a compression method that reduces the size of a partition. If Teradata Database cannot find a compression method that reduces the size of a partition, it does not apply a compression method. |
| NO AUTO COMPRESS for a column partition in the column list for the table | does not apply autocompression for physical rows. In this case, Teradata Database does apply any user-specified compression and, for column partitions with COLUMN format, row header compression, for the column partition. |