This topic presents case study scenarios to evaluate approaches to row partitioning a primary index on one level so that optimal benefit is realized.
- Scenario 1 is in a retail sales environment. The effects of row partitioning a sales table on months is examined from 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) shows 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 a table depends on the anticipated query mix. Use your extended logical data model as the starting point for making the decision, but test different scenarios to find the best partitioning scheme for your application and system configuration.
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 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 value of the partitioning column (200 partitions of equal size). | 99 |
| Update one column in each row that has a value for the partitioning column (200 partitions of equal size). | 98 |
| Test Description | Improvement |
|---|---|
| Teradata Parallel Transporter update operation to insert n rows into one of 200 partitions, where n is 1% of the table cardinality. | More than 10 times faster. |
| Teradata Parallel Transporter update operation to insert n rows into one of 200 partitions with one NUSI defined on the table, where n is 1% of the table cardinality. | More than 6 times faster. |