Advantages/Disadvantages of Partitioned Primary Indexes | VantageCloud Lake - Advantages and Disadvantages of Partitioned Primary Indexes

Advantages/Disadvantages of Partitioned Primary Indexes | VantageCloud Lake - Advantages and Disadvantages of Partitioned Primary Indexes - Teradata VantageCloud Lake

Lake - Database Reference

Deployment

VantageCloud

Edition

Lake

Product

Teradata VantageCloud Lake

Release Number

Published

February 2025

ft:locale

en-US

ft:lastEdition

2025-11-21

dita:mapPath

ohi1683672393549.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

ohi1683672393549

Advantages	Disadvantages
Row partition elimination enables large performance gains to be realizable, and these are visible to end users. For this optimization, more populated partitions are better than fewer populated partitions. Batch inserts and updates can run faster if the partitioning schema matches the data arrival pattern. This optimization is visible only to the DBA and operations staff. Finer granularity of partitions is better than a coarser granularity: daily is better than monthly. The largest performance improvements occur when there are no secondary indexes. Teradata Parallel Data Pump inserts and updates can benefit from more FSG cache hits because of the increased locality of reference when a target table is partitioned on transaction date. A finer partition granularity is better than a coarser partition granularity. Inserts into empty partitions are not journaled. This optimization is only invoked if the table has no referential integrity constraints.	Partitioned table rows are each 2 or 8 bytes wider than the equivalent nonpartitioned table row. Partitioned table rows are 4 bytes wider if multivalue compression is specified for the table. The extra 2 or 8 bytes are used to store the internal partition number for the row. You cannot define the primary index of a partitioned table to be unique unless the entire partitioning column set is part of the primary index definition. You can define a USI on the primary index columns to enforce uniqueness if the complete partitioning column set is not a component of the primary index definition; however, that adds different performance issues. Primary index-based row access can be degraded if the partitioning column set is not a component of the primary index. If you can define a secondary index on the primary index column set, then performance is independent of the number of partitions. If you cannot, or have not, defined a secondary index on the primary index, then having fewer partitions is better than having more partitions, whether achieved by means of the table definition itself or by row partition elimination during query processing.
Delete operations can seem instantaneous when the partitioning column set matches the retention policy, there is no secondary index defined on the partitioning column set, and the delete is the last statement in the transaction. If you delete all rows in a partition, there is no journaling of rows if no secondary index is defined on the partitioning column set. These properties may permit you to drop a secondary index or join index on the partitioning column set.	Joins of partitioned tables to nonpartitioned tables with the same primary index can be degraded. To combat this, observe the following guidelines: Identically partition all tables to be joined with the same primary index when possible and then join the tables on the partitioning columns. Fewer partitions, whether achieved by means of the table definition itself or by row partition elimination, are often better than more partitions for the nonpartitioned-to-partitioned join scenario.

Advantages

Disadvantages

Row partition elimination enables large performance gains to be realizable, and these are visible to end users.
For this optimization, more populated partitions are better than fewer populated partitions.
Batch inserts and updates can run faster if the partitioning schema matches the data arrival pattern. This optimization is visible only to the DBA and operations staff.
- Finer granularity of partitions is better than a coarser granularity: daily is better than monthly.
- The largest performance improvements occur when there are no secondary indexes.
Teradata Parallel Data Pump inserts and updates can benefit from more FSG cache hits because of the increased locality of reference when a target table is partitioned on transaction date.
A finer partition granularity is better than a coarser partition granularity.
Inserts into empty partitions are not journaled.
This optimization is only invoked if the table has no referential integrity constraints.

Partitioned table rows are each 2 or 8 bytes wider than the equivalent nonpartitioned table row. Partitioned table rows are 4 bytes wider if multivalue compression is specified for the table.
The extra 2 or 8 bytes are used to store the internal partition number for the row.
You cannot define the primary index of a partitioned table to be unique unless the entire partitioning column set is part of the primary index definition.
You can define a USI on the primary index columns to enforce uniqueness if the complete partitioning column set is not a component of the primary index definition; however, that adds different performance issues.
Primary index-based row access can be degraded if the partitioning column set is not a component of the primary index.
- If you can define a secondary index on the primary index column set, then performance is independent of the number of partitions.
- If you cannot, or have not, defined a secondary index on the primary index, then having fewer partitions is better than having more partitions, whether achieved by means of the table definition itself or by row partition elimination during query processing.

Delete operations can seem instantaneous when the partitioning column set matches the retention policy, there is no secondary index defined on the partitioning column set, and the delete is the last statement in the transaction.
If you delete all rows in a partition, there is no journaling of rows if no secondary index is defined on the partitioning column set.
These properties may permit you to drop a secondary index or join index on the partitioning column set.

Joins of partitioned tables to nonpartitioned tables with the same primary index can be degraded. To combat this, observe the following guidelines:
- Identically partition all tables to be joined with the same primary index when possible and then join the tables on the partitioning columns.
- Fewer partitions, whether achieved by means of the table definition itself or by row partition elimination, are often better than more partitions for the nonpartitioned-to-partitioned join scenario.