Consider the following points when you define the partitioning for a table or join index:
- You must choose a primary index, primary AMP index, or no primary index to achieve an even distribution of rows to the AMPs.
- When appropriate, construct the primary index or primary AMP index column from columns that are often constrained by equality conditions in queries to provide fast access, joins, and aggregations on those columns.
Do not add the partitioning columns to the primary index to avoid these concerns, because there is typically little or no benefit.
- If the partitioning columns are included in the set of primary index columns, then you can specify the primary index to be UNIQUE.
Adding a partitioning column as a primary index column only to make the primary index unique is not effective, because what must be unique is the original set of primary index columns, which may be used for access, joins, and aggregations.
- If the partitioning columns are also included in the set of primary index columns, you may want to define multiple combined partitions for primary index access and joins. Plan costing can be affected if there is a large number of empty combined partitions and PARTITION statistics are not collected. Other factors can also lead to having fewer combined partitions.
- If all of the partitioning columns are not included in the primary index column set, the primary index cannot be defined as a UPI.
If the primary index or primary AMP index columns must be unique, define a USI on them.
Because MultiLoad and FastLoad do not support USIs, you must use another load strategy such as any of the following:- Loading the rows using Teradata Parallel Data Pump (see Teradata® Parallel Data Pump Reference, B035-3021).
- Loading the rows into a staging table followed by an INSERT ... SELECT or MERGE into the target table using error logging (see CREATE ERROR TABLE , INSERT/INSERT ... SELECT , MERGE ).
- Dropping the USI, loading the rows using MultiLoad or FastLoad, and then recreating the USI (see Teradata® FastLoad Reference, B035-2411 and Teradata® MultiLoad Reference, B035-2409).
- Including the partitioning columns in the PI, noting the previously documented problems with this approach.
You must evaluate the tradeoffs among these choices carefully.
- If the primary index or primary AMP index columns need to be an efficient access path and there are multiple combined partitions, consider one of these options:
- Defining a USI on the primary index columns to improve access time.
- Defining a NUSI on the primary index columns to improve access time.
- Creating a join index to cover queries made against the table.
- Consider defining fewer combined partitions when a table is also accessed or joined on the primary index.