15.00 - Evaluating the Relative Merits of Partitioning Versus Not Partitioning - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Evaluating the Relative Merits of Partitioning Versus Not Partitioning

The following criteria provide a high‑level means for evaluating the relative merits of partitioning or not partitioning for a table.

Potential advantages of row partitioning.

  • The greatest potential gain in row‑partitioning a primary index is the ability to read a subset of table or join index rows instead of scanning them all.
  • For example, a query that examines two months of sales data from a table that maintains two years of sales history can read about of the table instead of having to scan it all.

    The advantages of row partition elimination can be even greater for multilevel partitioned tables (see the examples in the topic “Static Partition Elimination” in SQL Request and Transaction Processing for some remarkable scan optimizations).

    This provides the opportunity for a large performance boost to a wide range of queries. Importantly, the individuals who code those queries do not have to know the partitioning structure of the table and, as a result, there is no need to recode existing SQL applications.

  • Appropriate row partitioning can also facilitate faster batch data loads.
  • For example, if a table is partitioned by transaction date, the loading of transactions for the current day can be dramatically enhanced, as can the deletion of rows from the table that are no longer necessary.

    See “Performance Gains Realized From Row Partition Elimination” on page 401 for some examples of batch data loads running anywhere from six to ten times faster for a table having an appropriately defined partitioning versus the identical nonpartitioned table.

  • Row partitioning can make one or more existing secondary, hash, or join indexes redundant, which permits them to be dropped from the database.
  • Potential disadvantages of row partitioning.

  • Row partitioning can make single row (primary index) accesses to the table slower if a partitioning column is not a member of the primary index column set.
  • This disadvantage can be offset somewhat by using one of the following strategies:

  • Choose a partitioning column that is a member of the primary index column set.
  • Define a unique secondary index that can be used to make single row accesses to the table.
  • Constrain the values of the partitioning column set to enable the Optimizer to eliminate some row partitions when the query search conditions permit.
  • Row partitioning can make direct merge joins of tables slower unless both tables are partitioned identically.
  • This disadvantage can be offset when query search conditions allow some row partitions to be excluded from the join operation.

    The Teradata Database query optimizer has several special product join and merge join methods available to it just for joining row‑partitioned tables. See SQL Request and Transaction Processing for descriptions and examples of these join methods.

    As with other physical database design choices, you must always evaluate the respective tradeoffs of the decisions that are available to you by prototyping and testing their relative merits.