Guidelines for Specifying Table and Column Attributes for Column Partitioning - Teradata VantageCloud Lake

Lake - Database Reference
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
ohi1683672393549.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
ohi1683672393549
  • Both NoPI tables and column-partitioned tables are created with a MULTISET table type by default.

    Neither NoPI tables nor column-partitioned tables can be created as a SET table. Therefore, Vantage does not perform duplicate row checks for either.

  • Use the table-level option DATABLOCKSIZE if the default data block size is appropriate for the table. A data block size of 127.5 KB or less is recommended for a column-partitioned table.

    For Object File System tables, this is a default setting that you cannot change.

  • The settings for the table-level option FREESPACE may require adjustment for a column-partitioned table or join index with small internal partitions, as may be the case if a table or join index is also row partitioned, particularly if you add data incrementally to the table or index.

    These options specify the amount of space on each cylinder that is to be left unused during load operations. Reserved free space allows tables to expand within their allocated cylinders. This can prevent or delay the need for additional cylinders to be allocated, which incurs the overhead of moving data to the new cylinders. Avoiding new cylinder allocations can improve overall system performance.

    If you do a large INSERT ... SELECT operation and internal partitions are large or empty, little or no free space is needed. Keeping new table data physically close to existing table data and avoiding data migrations can improve overall system performance.

    For Object File System tables, this is a default setting that you cannot change.

  • Follow these column attribute guidelines when you create your column-partitioned tables.
    • Specify NOT NULL for columns that must not be null.

      Avoid nullable columns if possible. Nullable columns can significantly decrease the effectiveness of autocompression.

    • Specify column-level CHECK constraints when you can.

      CHECK constraints are valid for both NoPI tables and column-partitioned tables.

    • Specify UNIQUE and PRIMARY KEY constraints when you can.

      UNIQUE and PRIMARY KEY constraints are valid for both NoPI tables and column-partitioned tables.

    • Specify foreign key constraints whenever applicable.

      Foreign key constraints are valid for both NoPI tables and column-partitioned tables.

  • The following features are valid for both NoPI tables and for column-partitioned tables:
    • Fallback
    • Unique secondary indexes
    • Nonunique secondary indexes
    • Join indexes
    • Reference indexes
  • You cannot specify permanent journaling for a column-partitioned table or a NoPI table.
  • The following features are not shared by nonpartitioned NoPI tables and column-partitioned tables.
    • You cannot create a column-partitioned global temporary or volatile table.

      A nonpartitioned NoPI table can be created as a global temporary or volatile table.

    • A column-partitioned table can have an identity column, while a nonpartitioned NoPI table cannot.
    • The default behavior for creating a partitioned table without specifying a primary index or primary AMP index is NO PRIMARY INDEX.
    • NO PRIMARY INDEX is optional when you create a column-partitioned table, but you may need to specify NO PRIMARY INDEX for a nonpartitioned NoPI table.

      The default behavior for CREATE TABLE for a column-partitioned table is NO PRIMARY INDEX if you do not specify a primary index.

  • XML, BLOB, and CLOB are valid data types for both nonpartitioned NoPI tables and column-partitioned tables.
    The XML data type is only supported on the Block File System on the primary cluster. It is not available for the Object File System.
    There is a limit of 256M rows per rowkey per AMP with XML, BLOB, and CLOB data types. NoPI tables typically have only one hash value on each AMP, so the effective limit on the number of rows per AMP is approximately 256M.

    The exact number is 268,435,455 rows per rowkey per AMP.

    For column-partitioned tables, these limits are per column partition:hash bucket combination rather than PA and NoPI rows per hash value.