Re: How Do You Measure/Validate Compression Savings? [From Victor Sokovin: Wed, 14 Feb 2007 @ 12:41 GMT]

Message Posted: Wed, 14 Feb 2007 @ 12:41:35 GMT

Subj:

Re: How Do You Measure/Validate Compression Savings?

From:

Victor Sokovin

The uncompression is as near to free as it can be. Because it is value based rather than block based or run length or... and because we keep the list of values in memory any time we are accessing the table, we either do a pointer reference to the value in the current row or a pointer reference to the value in the value list based upon the setting of the compress bits for the column. There is a small cost for compressing it when the row is originally stored - have to compare the value to the list. And of course there is a one time cost to convert an uncompressed table to add compression. But for queries, including against small tables, it is a net win as long as the chosen compression reduces the size of the table.

Todd, thank you for reacting on the earlier posting but once you are on the subject can you also comment on the scenario I posted in my reply to James?

I'll quote it here again.

"Even if the columns get loaded compressed into the spool you can still do all kinds of operations on them (take simple concatenation, for example) and then you can compare thus built "derived" columns with, say, some other compressed columns. In order to compare things like that some uncompression seems to be very likely, and I don't see how uncompression does not affect CPU usage."

This is to describe what users may typically do with the compressed columns after they load the data in the spool and I wonder whether the Teradata code is consistent with "pointer reference to the value" or it may occasionally slip into copying and duplicating the values once complexity of operations increases (the latter would mean increase in resource consumption).

The code is of course not public domain and it is difficult to get access to an efficient lab able to measure all effects so I personally reserve the right of doubting about the end result but maybe you can confirm that in the scenarios I describe here Teradata will consistently use pointers? That would be interesting to know.

Regards,

Victor

Attachments

Library

Quick Reference

Archives of the TeradataForum

Message Posted: Wed, 14 Feb 2007 @ 12:41:35 GMT