Partitioning Expression Columns and the Primary Index
Follow these guidelines for the primary index and partitioning column set for a partitioned table.
The following additional characteristics are often desirable.
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.
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.
The following suggestions apply to this scenario.