15.00 - Determining an Optimal Partitioning Scheme - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
15.00
Content Type
User Guide
Publication ID
B035-1094-015K
Language
English (United States)

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.