Compression value Limit in TD12

General
Enthusiast

Compression value Limit in TD12

Hi,
I am trying to compress a column databasename which is defined as CHAR(30).
As a limit specified by teradata that it can compress upto 255 values.
I had put the script as
Alter table TESTDB.TEST_TBL
ADD DATABASENAME
COMPRESS ( .....255 values ....) ;

I am getting error as
5627:Column 'DATABASENAME' exceeds system limt for COMPRESS .

When I tried to reduce the columnvalues for compression.
When I specify 249 values It gets altered.For 250 to 255 values I get same error.

Also When I changed the column length to CHAR(40) to test.
It gets succeeded at 190 values .

Any idea on this ..
4 REPLIES
Junior Supporter

Re: Compression value Limit in TD12

RTFM:

"You cannot create a table if the expansion of its compression values exceeds the maximum size (8,192 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 8,192 character limit."

HTH.

Cheers.

carlos.
Enthusiast

Re: Compression value Limit in TD12

Hi,

I am doing compression on a table in TD13.10.

How to compress the value which has single quote in it.

The value is   SAINSBURY'S S/MKT  and the column is defined as varchar 22.

below is the sql I am trying to execute

Alter table $DB.MER_INFO

ADD MER_NAME VARCHAR(22)

COMPRESS   ('SAINSBURY'S S/MKT') ;

It gives error " query ends within a string or comment "

If I change as below 

Alter table D_BIWDB.MER_INFO

ADD MER_NAME VARCHAR(22)

COMPRESS   ('SAINSBURY''S S/MKT') ;

Then it compresses the value 'SAINSBURY''S S/MKT'.Shown in show table defination.

This is Rank 1 value ( 0.15M)  in the table of 1M.Not a single byte space savings due to above.

Can anybody help me to find how to compress a value having a single quotes in it.

Senior Supporter

Re: Compression value Limit in TD12

The '' syntax seems to work on my 13.10 system.

Can you run the below code and share the result? 

CREATE SET TABLE mvc_test1
(
var_type CHAR(1000),
id INTEGER)
UNIQUE PRIMARY INDEX ( id );

CREATE SET TABLE mvc_test2
(
var_type CHAR(1000),
id INTEGER)
UNIQUE PRIMARY INDEX ( id );

insert into mvc_test1
select 'this is a '' test',calendar_date - current_date
from sys_calendar.calendar
;

insert into mvc_test2
select *
from mvc_test1
;

select tablename, sum(currentperm)
from dbc.allspace
where databasename = 'yourdefaultdb'
and tablename in ('mvc_test1','mvc_test2')
group by 1
order by 1
;
alter table mvc_test2
add var_type compress ('this is a '' test')
;
select tablename, sum(currentperm)
from dbc.allspace
where databasename = 'yourdefaultdb'
and tablename in ('mvc_test1','mvc_test2')
group by 1
order by 1
;

drop table mvc_test1;
drop table mvc_test2;

Junior Contributor

Re: Compression value Limit in TD12

This works correct, it's compressing 'SAINSBURY'S S/MKT' as expected.

When you don't see any space reduction you might have different uppercase/lowercase notations, "Sainsbury's S/Mkt" will not be compressed.

CHAR columns must be analyzed case insensitive:

select MER_NAME (not casespecific), count(*)

from D_BIWDB.MER_INFO

group by 1

Another problem i've seen is trailing blanks in data:

select MER_NAME (not casespecific), count(*)

from D_BIWDB.MER_INFO

group by 1, char_length(MER_NAME)

Dieter