15.00 - General Rules for Column-Partitioned Tables - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

General Rules for Column‑Partitioned Tables

  • 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
  • Note: 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 by default.
  • You cannot specify COLUMN for more than one partitioning level in a table definition.

  • Teradata Database enforces PRIMARY KEY and UNIQUE constraints for column‑partitioned tables using a USI.
  • 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 Security Administration and SQL Data Control Language 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 the default specified by the AutoCompressDefault cost profile constant. For more information about AutoCompressDefault, see SQL Request and Transaction Processing.
  • 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 See “Partitioning CHECK Constraints for Partitioned Tables With 2‑Byte Partitioning” on page 630 and “Partitioning CHECK Constraint for Partitioned Tables With 8‑Byte Partitioning” on page 632 for further information about partitioning CHECK constraints.
  • The text for this partitioning constraint cannot exceed 16,000 characters; otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • You cannot specify CHARACTER SET KANJI1 for a character data column in a column‑partitioned table. Otherwise, Teradata Database aborts the request and returns an error to the requestor.
  • Instead, specify CHARACTER SET UNICODE.

  • A CREATE TABLE … AS source_table request 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 request for a column‑partitioned table creates an error table associated with that table.
  • In this case, Teradata Database creates the error table the same as it would for a non‑column‑partitioned table except that the error table is created as an unpartitioned 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 requests to update the rows of a column‑partitioned target table or to insert rows into a column‑partitioned target table.
  • Note: MERGE requests 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, the fallback data rows have the same primary index.

    If the primary data rows do not have a primary index, the fallback data rows also do not have a primary 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” on page 594 for column partition‑related performance considerations.

  • The following table presents the rules for using the NO AUTO COMPRESS option.

    IF you specify …

    THEN Teradata Database …

    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.