Table size, database space and archiving 1

[WILLIEWANKA]

I am currently part of a group of users within a database. We have a limited amount of space in which to create and store our tables. Is there any way we can archive or compress an entire table to save some space?

I have evaluated using COMPRESS on columns with only a couple of distinct values (nulls,indicators, codes, dates) and have saved about 25% of space. We'd like to make the implementation easier on the users so they don't have to go back and recreate each table with COMPRESS on each column. Is there another way to do this, or some other reccomendations?

 

[WILLIEWANKA]

Okay, another question. How does Teradata save space by compressing nulls, if there is no value to compress? And why doesn't it do this automatically?

 

[Mozc]

As for your second post - there are times when one may not want to compress a null and therefore it is left up to the DBA to decide. Savings is achieved on nulls because the fixed length bytes of nothing is no longer stored in the row. However, there is a null presence bit used to indicate the null which may require another byte if all 8 are already used. I agree that most of the time compressing nulls makes sense.

Right now, there's no automatic full table compress feature in Teradata. This would be very valuable as implementing compression can be very time consuming and the benefits quite large. To work around this issue I've developed a tool that automates as much of the process as possible - first collecting demographics on the desired tables and then applying single or multi-value compression for minimal space usage. While you are suggesting this for infrequently used tables, compression has a very important side benefit of reducing I/O on heavily used tables.

Let me also point out that you cannot nor should not compress all columns of a table. While there might be a number of columns where compression is straight-forward, many other situations require an understanding of the demographics and a systematic approach to optimizing compression.

I've seen a reduction in disk space upwards to 50% and a similar reduction in the amount of I/O required for queries. This has a direct impact on response times as well. Since CPU is required to drive I/O one would expect savings there too, but it generally is about one-half to one-fifth of the other metrics.

If you are interested in knowing more about how you can do the same, just ask.

Doug Drake
(MOZC)

 

[WILLIEWANKA]

Great, thanks for the feedback.