Teradata Row Level Partitioning – Save Permanent Space

DWH Pro Admin

October 6, 2021

minutes reading time

Block Level Compression (BLC) is always enabled in the latest generation of Teradata systems. Usually, the compression factor is shallow when Multi Value compression is used.

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.

For this, it is essential to understand how Block Level Compression and Row Partitioning work.

BLC packs whole data blocks using different algorithms. These include ZLIB (software compression) and ELZS_H (hardware compression) used in 9 different compression levels.

It is essential 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. Take a table, for example, which contains a date field and has an arbitrary unique primary index (UPI).

If we do not use row partitioning, 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 spreads perfectly over all AMPs.

Next, let’s consider how the data of a Row Partitioned Table is distributed. 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.

Precisely from this fact, we can achieve the space advantage results together with BLC: Since the exact date values are close in the same data blocks, the compression algorithms achieve a higher compression rate.

In practice, we are talking about up to 50% smaller tables under ideal conditions!

Of course, keeping the overall design in mind is always essential. Using row partitioning only to save space may make sense for temporary tables, not production tables.

So in the future, if you run out of space again, it is well worth considering this option.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like