Column compression and Table header size

Database
Enthusiast

Column compression and Table header size

I have to implement the column compression on huge table with several columns.As I read in the manuals for compression, number of compressible/compressed values for a particular table is dependent on size of table header row.

Now i have the following questions

(1) How to know the size of table header?
(2)What is the maximum number of compressible columns for a table?

my idea is to reduce the number of compressible values per column per table to make them fit into the table header row perfectly.can anyone advise me on this?
8 REPLIES
Enthusiast

Re: Column compression and Table header size

Given that the current table header size is around 128K, you shouldn't have too much (uh ?) problems.

I am not aware of a way to check the size of the table header,

but if you are interested, there's the "Table header format" section in the Design manual, which seems to be a good place to start. :)

Good luck !
Enthusiast

Re: Column compression and Table header size

I remember seeing in some document that the max size of the compressed values cant exceed 8192 characters.

So make sure you dont compress too many values(across columns) which exceeds this size.

Regards,
Annal T
Junior Contributor

Re: Column compression and Table header size

"Table header size" is the size of the table without any data in it:

create table dropme as exsiting_table with no data;
select min(currentperm) from dbc.tablesize
where databasename = database and tablename = 'dropme';
drop table dropme;

Dieter
Enthusiast

Re: Column compression and Table header size

Leo Issac,

Following I am inserting my 2 cents to help you:

1) The size of table header is variable, and can range from 512 to 130,560 bytes (1 to 255 sectors).
The table header must fit at maximum a data block (V2R6.1).

2) The limits that I know are:
. You can compress 255 distinct values for an individual column at maximum;
. There is no number of columns limit, as far as I know, but the the amount of compression values
should not exceed 8,192 bytes.

Enthusiast

Re: Column compression and Table header size

Hi Dieter,

Any specific reason for selecting Min(currentperm) and not Max(Currentperm) for that table?

Regards,
Annal T
Junior Contributor

Re: Column compression and Table header size

Hi Annal,
you can use MIN/MAX/AVG because the table header is stored on each AMP, so it's the same size per AMP

Dieter
Enthusiast

Re: Column compression and Table header size

Hi Dieter & Teradata Gurus,

What is the maximum row length in TD 12 allowed ? &
I am creating the below table, and get the error msg (Maximum Possible row length in the table is too large ) & is it due to the max limit crossed of the table header ?

create table x ( i varchar(64000) , j varchar(250) )

whereas when i create a table with j column as varchar(200) , it allows me to create the table. Please suggest.

Re: Column compression and Table header size

The maximum row length is approximately 64 KB (the actual limit is 64,256 bytes)