Archives of the TeradataForum

Message Posted: Wed, 19 Jan 2011 @ 10:14:52 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I wrote a compression process a some time back and, coincidentally, have just started using it now for real.

The approach I took was this ...

Use a Stored Procedure to derive, Column by Column (for a given Table, supplied as an input variable), the best Compression values and put those values in a Table.

There were two additional parameters that I found useful :

i) a percentage figure which states that, for any given Column, that figure must be met in terms of space saved before even bothering in storing Compressed values away.

ii) a second percentage figure which specifies that, before allowing the next level of Compression values (for example going from 15 to 31 values), the benefit in doing that must achieve at least that additional space saving.

So, I tend to have input values of 15, 5 (i.e. each Column must at least saving 15% space, even to bother with Compression, and then each level after that must save an additional 5%)

Adding up the space taken by a Column is relatively easy, but you must also take into account the overhead taken up by any particular set of values; this is what gives you the net space saving. I looked at the Teradata algorithm for this but it did my head in, so I blagged a rough and ready alternative (but happily it's quite accurate in most cases)

From there, I used a scripting language to read the values from the Table of compressed values and 'weaved' those values into the Create Table statement.

Whilst on reflection I may have over-engineered the process, the whole thing is no trivial task.

Prior to this approach, I used to generate a series of Group By statements for the Columns in a Table, and run them in SqlAssistant (outputting the values to a file). I then went through and, without too much scientific thought, manually put a Compression string together for each Column in the Table.

My personal recommendation is, unless you're up for a challenge, look for a Tool on the Market Place that does the job !

Regards

David Clough
Database Developer
Database Design Group

	<Prev	Next>		<<First	<Prev	Next>	Last>>