Advantages/Disadvantages of Partitioned Primary Indexes | Teradata Vantage - Advantages and Disadvantages of Partitioned Primary Indexes - Advanced SQL Engine

Advantages/Disadvantages of Partitioned Primary Indexes | Teradata Vantage - Advantages and Disadvantages of Partitioned Primary Indexes - Advanced SQL Engine - Teradata Database

Database Design

Product

Advanced SQL Engine

Teradata Database

Release Number

17.10

Published

July 2021

Language

English (United States)

Last Update

2021-07-27

dita:mapPath

kko1591750222108.ditamap

dita:ditavalPath

kko1591750222108.ditaval

dita:id

B035-1094

lifecycle

Product Category

Teradata Vantage™

Advantages	Disadvantages
Row partition elimination enables large performance gains to be realizable, and these are visible to end users. For this particular optimization, more populated partitions are generally 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 generally 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. In this case, a finer partition granularity is generally 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 be nearly 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. You can delete all of the rows in a partition if you want to do so. In this case, there is no journaling of rows if no secondary index is defined on the partitioning column set. These properties might 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 them 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 particular optimization, more populated partitions are generally 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 generally 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.
In this case, a finer partition granularity is generally 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 be nearly 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.
You can delete all of the rows in a partition if you want to do so. In this case, there is no journaling of rows if no secondary index is defined on the partitioning column set.
These properties might 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 them 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.