16.20 - Partitioning Expression Columns and the Primary Index - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)
For best practice, use these guidelines for the primary index and partitioning column set for a partitioned table. The primary index should:
  • Distribute rows evenly across the AMPs.
  • Facilitate direct row access.
  • Be useful for satisfying join conditions.
  • Be useful for aggregation.

For a unique primary index, you must include the partitioning column in the primary index, if the column is in the set of columns you want to be unique. Otherwise, the primary index can be nonunique and you can use a unique secondary index (USI) to enforce uniqueness on the primary index columns.

Loading Data into a Table with a Unique Secondary Index (USI)

Neither MultiLoad nor FastLoad can load rows into a table that has a USI. See Teradata® MultiLoad Reference, B035-2409 and Teradata® FastLoad Reference, B035-2411.

You can either use the Teradata Parallel Data Pump utility or you can use a batch load with error tracking. See Teradata® Parallel Data Pump Reference, B035-3021.

The method for performing a batch load is as follows:
  1. Create a temporary transitional table with the same definition as the target table except for USIs, join indexes, hash indexes, referential constraints, or triggers.
  2. Use either an INSERT … SELECT or MERGE batch load with error tracking to move the bulk loaded rows into the target table.

    For details about INSERT … SELECT and MERGE, see Teradata Vantage™ SQL Data Manipulation Language , B035-1146 . For information about error tables, see CREATE ERROR TABLE.

Planning Partitioned Primary Indexes

Although a primary index can have many partitions for access and joins, 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 may not provide the best result. 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 the 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.