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. |