Row-partitioning this table has more advantages than disadvantages. The partitioned table requires 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.
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 |
|
| 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. |