15.00 - Guidelines for Partitioning Column-Partitioned Tables and Join Indexes for Column Partitioning - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Guidelines for Partitioning Column‑Partitioned Tables and Join Indexes for Column Partitioning

  • While you can define column partitioning at any level of multilevel partitioning, in most cases you should follow these guidelines when you configure the partitioning for a column‑partitioned table.
  • Code the column partitioning level first and follow the column partitioning level with any row partitioning levels.
  • If you do not code the column partition at the first level, code it as the second level after DATE or TIMESTAMP row partitioning.
  • Some considerations that might lead to putting the column partitioning at a lower level are the following.

  • Potential improvements for cylinder migration.
  • Block compression effectiveness.
  • If you specify row partitioning as part of a multilevel column partitioning for a table, consider specifying the ADD option for the any partitioning levels that might need to increase their number of partitions in the future.
  • Unless you have a good reason not to, you should use the defaults when you specify PARTITION BY COLUMN. Do not override the defaults without first giving the reasons for doing so serious consideration
  • For columns that are often specified in queries, but where the specific set of columns specified varies from request to request, you should create single‑column partitions for the frequently specified columns.
  • Use ROW format for wide column partitions because it has less overhead than a container that holds one or a few values.
  • If Teradata Database does not assign ROW format for a column partition, but you have determined that ROW format is more appropriate because it decreases space usage, specify ROW explicitly.

  • Use COLUMN format for narrow column partitions, especially if autocompression is effective.
  • If Teradata Database does not assign COLUMN format for a multicolumn partition, but COLUMN is user-determined to be more appropriate (decreases space usage, etc.), specify COLUMN explicitly.

    You might need to specify COLUMN format explicitly for a multicolumn partition that contains a column with a VARCHAR, VARCHAR(n) CHARACTER SET GRAPHIC, or VARBYTE data type and defined with a large maximum value, but where values are actually very short in most cases. This is because the system‑determined format might be ROW based on the large maximum length.