Teradata Compression with the DWHPRO TITHONIZER
Do you work in an environment where a Teradata compression tool is installed that works to your satisfaction?
If not, I assume you are probably not very pleased with having to walk your way through the databases, table by table, to find out what to compress on which column.
Typically, this is time-consuming, fatiguing and subject to momentary human judgment to some extent.
Every reevaluation is costly again. Often, therefore, Teradata compression activities stop once the largest decile of tables has been treated, leaving a lot of potential unused. I can offer you a solution to this problem:
What Is The Teradata DWHPRO TITHONIZER?
Inspired by the Greek legend persona of Tithonos, whose fate it was to shrink ever more, DWHPRO TITHONIZER determines the best possible and feasible current compression potential for a table, under given constraints and assumptions.
DWHPRO TITHONIZER determines a set of compression candidate columns and the compression candidate values per column.
It then selects the one combination that achieves the highest savings under the given technical constraints and with the current table demographics.
It can be used for compressing a table for the first time or an update on given compressions.
It does not apply the compression findings directly. Rather, it stored them in a separate table to be implemented at a later stage.
This allows you to separate the potentially resource-intense analysis from the application work.
Also, DWHPRO TITHONIZER can be applied to one table in isolation or all of the tables of a database. This enables you to perform demo or study runs over representative tables before any mass activity.
The core assumption behind DWHPRO TITHONIZER is that it is possible to determine the one combination of compressions over columns that achieves the highest net benefit under the given circumstances and constraints.
It gathers the following information:
- Is the table populated by enough rows for compression to have a savings potential at all?
- Which columns can be candidates for compression for technical reasons?
- Which columns shall be candidates for compression from a business or policy point of view?
- Which values, given columns, are candidates?
- What is the benefit of saved bytes of compressing one value?
- What is the cost of compressing this one value?
- What is the Presence Byte consumption of a given compression in total?
For every column, compressed value sets are summarized in levels that represent one Presence Bit consumed each.
Based on this information, a dominant path for compression is determined by ordering the various levels of columns by the “savings per header cost ratio”.
Highly frequent level 1 values with little cost in bytes for the table header come first in line. At the other end, one will find lengthy and infrequent values with a razor-thin or even no net benefit.
After potentially adjusting for the net benefit and local Presence Byte break-even points, the dominant path is translated into a series of compression statements that are stored in the result table TITHONOS. From there, alter table statements can be generated to apply the compression.
For a detailed description of DWHPRO TITHONIZER, please refer to the pdf file attached to this article:
I recommend to read it before you start working with the Stored Procedure.
DWHPRO TITHONIZER was developed and tested for Teradata Versions 13 and 14, 15, and 16.
Improvements, refinements or modifications of functionality or methodology will occur as experience with it is gained and time to do it is available.
I also hope for valuable input from you as our reader. If you need additional features or find any bugs, just send us an email:
Feel free to use or modify DWHPRO TITHONIZER for your training or working purposes. But please leave a note in the program code that the original version is from DWHPRO.
Before you start finding out what the Stored Procedure can do for you, let me remind you that the responsibility for the use of the Stored Procedure and any consequence thereof lies wholly in the hand of whoever uses it.
I am not responsible for any damage, misuse or other negative consequences that emerge out of DWHPRO TITHONIZER applications.
If you have gathered experience, I would be pleased to hear of any results you achieved with it: