Partitioning Expression Columns and the Primary Index - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
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.