15.00 - Partitioning Expression Columns and the Primary Index - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

Partitioning Expression Columns and the Primary Index

Follow these guidelines for the primary index and partitioning column set for a partitioned table.

  • Irrespective of the relative partitioning considerations that must be balanced, always keep in mind that you must always choose a primary index that meets the following fundamental requirement.
  • The index must distribute rows evenly across the AMPs.
  • The following additional characteristics are often desirable.

  • The index must facilitate direct row access.
  • The index must be useful for satisfying join conditions.
  • The index must be useful for aggregation.
  • It is always best if the partitioning column set is a component of the primary index; however, this is not always possible.
  • If the primary index contains the entire partitioning column set, they you can define it as unique.
  • If necessary, you can define a USI on the primary index column set to enforce uniqueness.

    Note that neither MultiLoad nor FastLoad can load rows into a table that has a USI. See Teradata MultiLoad Reference and Teradata FastLoad Reference for details.

    You can either use the Teradata Parallel Data Pump utility to circumvent this limitation, or you can use a batch load with error tracking. See Teradata Parallel Data Pump Reference for details.

    The method for performing a batch load is as follows.

    a Create a temporary transitional table with the same definition as the target table except for USIs, join indexes, hash indexes, referential constraints, or triggers.

    b Use either an INSERT … SELECT or MERGE batch load with error tracking to move the bulk loaded rows into the target table (see SQL Data Manipulation Language for details about INSERT … SELECT and MERGE. See “CREATE ERROR TABLE” on page 228 for information about error tables.

  • While it can be acceptable to have many partitions for primary index access and joins, keep in mind that Optimizer costing can be affected negatively if there are too many unpopulated partitions.
  • Be aware that simply adding a partitioning column set to the primary index definition typically is not a good idea.
  • Instead, you should carefully plan your primary index and partitioning expression member columns in such a way that neither detracts from the other. In the ideal case, the primary index is defined identically with the partitioning column set in a manner that supports all of its possible uses equally well.

  • You cannot specify an EACH clause for character‑ or graphic‑based test values in partitioning expression that is defined using a RANGE_N function.
  • If the primary index does not contain the entire partitioning column set, then you cannot define it to be unique.
  • The following suggestions apply to this scenario.

  • If the primary index columns must be an efficient access path, and there are many partitions, consider defining a USI or NUSI on the primary index column set.
  • Consider defining fewer partitions when the table must also be accessed, joined, or aggregated on the primary index.