Archives of the TeradataForum

Message Posted: Thu, 08 Nov 2007 @ 09:46:13 GMT

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

Good day all.

We are busy trying to implement a structured approach to Multi Value Compression (MVC).

However, we have run into the following snag:

5627: Column 'OBJECTNAME' exceeds system limit for compress.

The reason for this problem is that we have identified 255 (256 including null) compressible values for the specific column which is unicode char(30) (i.e. 60 bytes), but the TABLE HEADER is not able to store them all.

QUESTION: How do I calculate how many of these 255 candidate values (null included by default) will fit in the TABLE HEADER?

I checked the Database Design Manual, and there is a section called: "Tradeoffs Between Compression and the Net Capacity Required to Store Compressed Values"

In this section, a non-linear GRAPH is shown to illustrate the trade-off between the "number of compressed values" (0 to 256) and the "field width" (0 to 256 bytes). However, I CANNOT LOCATE AN ALGORITHM for doing the calculation to derive the same graph.

I have also come across the following information in the manual, but these do not provide a full picture as yet:

1. Table Header can be a Thin Header (64 KB, or one row), or a Fat Header (128 KB, or two rows).

2. Field 5 (56350 bytes) in the table header caters for (contains, amongst other things) compressed values.

3. You cannot create a table if the expansion of its compression values exceeds the maximum size (8192 characters) of the CompressValueList column in the DBC.TVFields system table. As a guideline, note that 255 CHARACTER(29) values are barely contained within the 8192 character limit.

The above information from the manual is a bit confusing because:

a) Is 8192 characters the only absolute cut-off point I should worry about? In other words, is this the only real constraint I should use to calculate how many values I could compress without running into systems limits?

b) Under which conditions would you get a thin header vs a fat header?

c) Does thin vs. fat header have any impact here? I.e., could this become 2x8192 for a fat header?

d) Are MVC values stored as varying length values in the table header, or is the datatype&size of the compressed column used here?(i.e. padded with spaces).

e) Does UNICODE/2-byte char columns have a different impact on the amount of header space used than LATIN/1-byte character columns? In other words, are these 8192 character positions, or 8192 Bytes?

Kind Regards

Etienne Stieger
Analytics and Information Management (AIM/EIW) Standard Bank of South
Africa Ltd

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