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

Teradata Vantageā„¢ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)
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.