15.00 - Conclusions - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Conclusions

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:

 

             Activity

  Nonpartitioned Table

     Partitioned Table

        Improvement

         Comments

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