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.
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.
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.
Potential disadvantages of row partitioning.
This disadvantage can be offset somewhat by using one of the following strategies:
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.