Block Level Compression (BLC) is always enabled in the latest generation of Teradata systems. Usually, the compression factor is very low when Multi Value compression is used additionally.
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 Row Partitioning to save space.
For this, it is important to understand how Block Level Compression and Row Partitioning work.
BLC packs whole data blocks, using different algorithms. These include ZLIB (software compression), ELZS_H (hardware compression) which are used in 9 different compression levels.
It is important to mention that BLC is not used for performance tuning because the data blocks have to be decompressed in memory when accessed. But that’s not what this article is about, we want to save space.
BLC works better the more equal values can be compressed in a data block. Let’s take a table as an example, which contains a date field and has an arbitrary unique primary index (UPI).
If we do not use row partitioning, then the different date values will be randomly scattered across the AMPs, because as we know the rows are distributed according to the primary index. A UPI distributes perfectly over all AMPs.
Next, let’s consider how the data of a Row Partitioned Table is distributed. Here, the data is first distributed to the AMPs according to the UPI, then the rows are sorted into the target partitions according to the partition expression.
In our example, the partition expression could be based on the date. The goal of the Row Partitioning is then to store the rows with the same date in the same partition because this means at the same time that these are physically located on the disk next to each other and can be accessed efficiently.
Exactly from this fact also the space advantage results can be achieved together with BLC: Since the same date values are close together in the same data blocks, the compression algorithms achieve a higher compression rate.
In practice, we are talking here about up to 50% smaller tables under ideal conditions!
Of course, it is always important to keep the overall design in mind. Using row partitioning only for the purpose of saving space may make sense for temporary tables, but not for production tables.
So in the future, if you run out of space again, it is well worth considering this option.