15.00 - Guidelines for Specifying Table and Column Attributes for Column Partitioning - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Guidelines for Specifying Table and Column Attributes for Column Partitioning

  • Both nonpartitioned NoPI tables and column‑partitioned tables are created with a MULTISET table type by default.
  • Neither nonpartitioned NoPI tables nor column‑partitioned tables can be created as a SET table. As a result, Teradata Database does not perform duplicate row checks for either.

  • Use the system default settings for the table‑level option DATABLOCKSIZE and the DBS Control parameter PermDBSize for a column‑partitioned table unless performance analysis indicates otherwise.
  • The settings for the table‑level option FREESPACE and the DBS Control parameter FreeSpacePercent might require adjustment for a column‑partitioned table or join index with small internal partitions, as might 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, and the reserved free space allows table data to expand on current table cylinders, preventing or delaying the need for additional table cylinders to be allocated, preventing or delaying data migration operations associated with new cylinder allocations.

    If you do a large INSERT … SELECT operation and internal partitions are either 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.

  • Follow these column attribute guidelines when you create your column‑partitioned tables.
  • Specify NOT NULL for columns that should not be null.
  • Because nullable columns can significantly decrease the effectiveness of autocompression, you should avoid them unless you have a sound reason for specifying them.

  • 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 they are applicable.
  • Foreign key constraints are valid for both NoPI tables and column‑partitioned tables.

  • Many considerations for NoPI tables that are not column‑partitioned also apply to column‑partitioned tables because they are also NoPI tables.
  • The following features are valid for both NoPI tables and for column‑partitioned tables:

  • Fallback
  • Unique secondary indexes
  • Non‑unique secondary indexes
  • Join indexes
  • Reference indexes
  • The following SQL statements and Teradata Tools and Utilities utility are not allowed on nonpartitioned NoPI tables and column‑partitioned tables:
  • UPDATE (Upsert Form)
  • MERGE
  • MultiLoad
  • For a primary‑indexed table, Teradata Database generates the hash value of a row from the values of the columns that constitute the primary index. This hash value determines to which AMP a row is sent and stored. Although neither NoPI tables nor column‑partitioned tables have a primary index, each row still must be assigned to a hash bucket, and the bucket number to which the row is assigned is generated internally.

    This approach allows fallback and index maintenance to work as they would if the table had a primary index.

  • 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 setting of the DBS Control PrimaryIndexDefault parameter does not affect the default primary index specification if PARTITION BY is specified. The default behavior for creating a column‑partitioned table without specifying a primary index is always NO PRIMARY INDEX.
  • You are not required to specify NO PRIMARY INDEX when you create a column‑partitioned table, but you may need to specify NO PRIMARY INDEX for a nonpartitioned NoPI table, depending on the DBS Control settings.
  • The default behavior for CREATE TABLE for a column‑partitioned table is NO PRIMARY INDEX if you do not specify a primary index. The setting of the PrimaryIndexDefault DBS Control parameter does not affect this behavior.

  • XML, BLOB, and CLOB are valid data types for both nonpartitioned NoPI tables and column‑partitioned tables.
  • Note: There is a limit of 256M rows per rowkey per AMP with XML, BLOB, and CLOB data types. NoPI tables normally 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 rows per hash value.

    See the following topics for detailed performance guidelines for specifying compression, I/O, CPU usage, and storage operations, and collecting statistics for column‑partitioned tables and join indexes.

  • “Guidelines for Specifying Compression for Column‑Partitioned Tables and Join Indexes” on page 311
  • “Guidelines on Optimizing I/O Operations, CPU Usage, and Disk Space Usage for Column-Partitioned Tables and Join Indexes” on page 312
  • “Guidelines for Collecting Statistics on Column-Partitioned Tables and Join Indexes” on page 313