Teradata Compression Tool: AtanaSuite

Teradata Compression Tool

Teradata supports compression from version 12 onwards. Unlike some other databases, we cannot do this command:

alter table daily_inventory compress; // We can’t do this in Teradata

We need to specify what column values we like to compress and we can only pick up to 256 of them. Well, what happens if data changes and if my top 256 is no longer valid. I guess, you need to recompress using most recent table definition. Though compression is cumbersome, Teradata highly recommends using compression for large (fact) tables. We all agree that Teradata need to come up with a better tool or command to implement compression.

Tool

AtanaSoft has many tools that only work on Teradata. They came up with a nice tool called “AtanaSuite” and includes an optional add on for compression. This is easy to use and quite intuitive, no training is recommended. They produced a web seminar that can help us to understand this product better.

 

[For security reasons, text on part of this screen is erased].

Connect and analyze

It is as simple as connect this tool to Teradata and start analyzing. This tool is based on .NET and requires little configuration to connect. Start a new session with “File/New/Compression tool”. This gives an above screen in which “selection” and “options” tabs are enabled.

This tool will list all tables in that schema. Choose a table that you like to compress. This is where the second tab comes handy. As you can see this tool allows:

• Sample data from a table (like 1%).
• Sample using first N rows (such as 1000 rows).
• Full table.

Once you run this tool, it returns quickly; that is, if you have a table > 1TB, it completes full table analysis within a couple of hours. After completion, this tool gives DDL for creating a backup table (under scripts tab). This script does not result in changing the original table itself.

What if scenario

As you can see in the second image, you can ask this tool what if “Change CHAR to VARCHAR” is applied on a table. What compression rate can be expected. Likewise, a number of other options are present in this second tab. You can choose as many check boxes as applicable to see what compression ratio can be obtained.

You may also assign dollar value for storage and will tell you how much storage savings are expected.

Compression ratios

From my experience, these compression rates are quite typical. Obviously, this depends greatly on your data and compression algorithm picked up by database:

• Teradata: less than 1 to 1.5.
• Oracle: About 2, depends on PCTFREE, etc., among other factors.
• Datallegro: Typically 4, can be up to 6.
• Vertica: Up to 10 or more.

As I said, this is just my observation. Your mileage may vary.

Performance versus storage space

Compression is meant for saving storage. I am yet to come across a situation where database performance improves after compression. Vendors always tell you performance (throughput or response time) greatly improves after compression.

Advertisement