This topic presents several case study scenarios to evaluate various approaches to row partitioning a primary index on one level in such a way that optimal benefit is realized.
- The first scenario (see Scenario 1) is set in a retail sales environment. The effects of row partitioning a sales table on months is examined from several different perspectives.
- The second scenario (see Scenario 2) is a continuation of the first and attempts to boost the benefits of using a partitioned table further by partitioning on days rather than months. The concept is to investigate how the number of row partitions affects query workload performance.
- The third scenario (see Scenario 3) is set in a telecommunications environment. The effects of partitioning a telephone call tracking table on call date and phone number are compared and contrasted for the same query workloads.
- The final scenario (see Scenario 4) illustrates how the decision whether to use row partitioning is often multidetermined, requiring a solution based on numerous, carefully weighted factors.
Determining an Optimal Partitioning Scheme
An optimal partitioning scheme for any table depends on the anticipated query mix. Use your extended logical data model as the starting point for making the decision, but you should always test a few different scenarios to ensure the best partitioning scheme for your particular application and system configuration.
Note that these scenarios are intended to make specific points about partitioning and are not meant to be taken as industry-specific partitioning recommendations.
The following tables list the results of a test prototype performed to examine the potential improvements of using partitioning instead of not using partitioning for the same table.
| Test Description | Reduction in Elapsed Time (percent) |
|---|---|
| Select all rows with a particular value of the partitioning column (200 partitions with roughly the same number of rows each). | 98 |
| Select a month of activity from one partition containing six months of data (11 years of data contained in 40 partitions of unequal size). | 97 |
| Delete all rows with a particular value of the partitioning column (200 partitions of equal size). | 99 |
| Update one column in each row that has a particular value for the partitioning column (200 partitions of equal size). | 98 |
| Test Description | Improvement |
|---|---|
| Teradata Parallel Transporter update operation to insert a number of rows equal to 1% of the table cardinality into one partition out of 200 total partitions. | More than 10 times faster. |
| Teradata Parallel Transporter update operation to insert a number of rows equal to 1% of the table cardinality into one partition out of 200 total partitions with one NUSI defined on the table. | More than 6 times faster. |