The latest generation of Teradata systems always has Block Level Compression (BLC) enabled. When using MultiValue compression, the compression factor is typically low.
How Block Level compression and MultiValue compression relate to each other is shown in detail in the article below:
In this article, we will show you a trick on how to use Block Level compression together with Teradata Row Level Partitioning to save space.
To comprehend this, it is crucial to grasp the functioning of Block Level Compression and Row Partitioning.
BLC packs whole data blocks using different algorithms. These include ZLIB (software compression) and ELZS_H (hardware compression), used in 9 different compression levels.
BLC is not utilized for performance tuning due to the need to decompress data blocks in memory upon access. However, this article’s focus is on space conservation.
BLC is more effective as it compresses equal values in a data block. Consider a table with a date field and a unique primary index (UPI).
Due to the primary index distribution, date values will be distributed randomly across the AMPs without row partitioning. A UPI will distribute evenly across all AMPs.
Let’s discuss how data in a Row Partitioned Table is distributed. Initially, the data is distributed to the AMPs based on the UPI. Following that, the rows are sorted into the designated partitions according to the partition expression.
For our example, we can base the partition expression on the date. The aim of Row Partitioning is to store rows with identical dates in the same partition. This ensures they are physically adjacent on the disk, enabling efficient access.
Due to this fact, BLC and space advantage results can be attained concurrently. The compression algorithms attain greater compression rates as the precise date values are located closely within the same data blocks.
In practice, ideal conditions can result in up to 50% smaller tables.
It is important to consider the overall design when utilizing row partitioning. While it may be appropriate for temporary tables, it should not be used solely for space-saving purposes on production tables.
In the future, should you encounter insufficient space again, it would be worthwhile to contemplate this alternative.