15.00 - Conclusions - Teradata Database

Teradata Database Design

Teradata Database
User Guide


The results show there are few disadvantages, and some significant advantages, to row partitioning this table given the workloads that access it. The partitioned table requires somewhat more disk space than its nonpartitioned counterpart. There is a 2-byte or 8‑byte partition number recorded in each row that consumes additional storage space; however, the percentage increase seen for most row sizes does not exceed about 5%, and is often considerably less than that.

The following table summarizes the improvement opportunities for this case study:



  Nonpartitioned Table

     Partitioned Table



Monthly delete of one month of data

Teradata Parallel Transporter job reads most blocks, updates most blocks

ALTER TABLE statement deletes partition

Much faster performance

Easier maintenance

Nightly inserts

Inserted rows scattered throughout table

Inserted rows concentrated in one partition

Faster performance

No changes to load script needed

Primary index access

1 block read

1 block read

No change

No SQL changes needed

Comparison of current month to prior month

All blocks read

2 partitions read

Step is 12 times faster (only 2 of 25 partitions read)

No SQL changes needed

Trend analysis over entire table

All blocks read

All blocks read

Little change

  • Rows are two bytes longer for partitioning
  • 2% more blocks for 100 byte rows
  • Joins

    No direct Merge Joins

    No direct Merge Joins

    Little change

    No direct merge joins in this example because of the choice of primary index.

    Scenarios Scenario 2