how does compress work in tables

Database

how does compress work in tables

Dear experts,

I am very new to teradata and  have TD query as:

CREATE SET TABLE tablename(

fieldname DECIMAL(15,2) COMPRESS (0.00 ,50.00 ,46.00 )

);

What the value will be entered in this fieldname if i insert 1000.594 in the table?

What is the difference between values of  DECIMAL(15,2)  and DECIMAL(15,2) COMPRESS (0.00 ,50.00 ,46.00 ) ?

7 REPLIES
Enthusiast

Re: how does compress work in tables

COMPRESSION is a teradata feature which allows the users to compress the most commonly appearing values in once column. Physically, the compressed values are stored in table header and not reapeating in each row thus saving some space. If a value other than the compressed list comes with a row, that takes its normal allocated space...

COMPRESS (.....) clause defines the values that are compressed.

In your case only 0.00, 50.00 and 46.00 are the values that will be compressed, so if you enter 1000.594 it is not compressed but consumes all the allocated bytes.

Fan

Re: how does compress work in tables

Hi

Yep ,as Qaisar said,If the compress the column values which they are repeating and keep those columns as compress,so we can save some space .We would observe the saved space ,If it is a huge data or there more repeating column values exist.

Here nothing much difference , DECIMAL(15,2)  are not compressing or not saving space and DECIMAL(15,2) COMPRESS (0.00 ,50.00 ,46.00 ) are compressing with repeated columns or are saving some space,Thats all

Thanks

Re: how does compress work in tables

Hi,

Can you let me know what is the checksum and checksum default which is specified while creating a table.What is the use of it and how does it perform.

Thanks,

Prathyusha

Highlighted
Enthusiast

Re: how does compress work in tables

Is there a way to define a range ? I have a table create that does compress on 5 date fields like

below and there are 105 dates for each date filed ...any way to get around not having to hard code all values ?


opn_dt DATE FORMAT 'YYYY-MM-DD' COMPRESS

(DATE '1994-08-01',DATE '2001-12-01',DATE '2003-04-01',
DATE '2006-01-01',DATE '1988-09-01',DATE '1990-01-01',
DATE '1997-05-01',DATE '2004-09-01',DATE '2000-02-01',
DATE '2007-06-01',DATE '1991-06-01',DATE '1998-10-01',
DATE '1992-11-01',DATE '1994-03-01',DATE '2001-07-01',
DATE '2008-11-01',DATE '1988-04-01',DATE '1995-08-01',  etc

Senior Apprentice

Re: how does compress work in tables

Hi,

Not when using Multi-Value Compression (MVC) which is what is being used in your example. You have to code each value.

Algorithmic Compression (ALC) would allow you to compress a known rage of date values, but those would have to be hard-coded in your ALC routine - which you would have to write.

If you're requirement is to use MVC against a known range of dates then you could write some SQL which would build the list of values for you. Then you simply cut'n'paste the build list into your table definition.

Something like:

SELECT ',DATE'''||(BEGIN(date_range) (FORMAT 'yyyy-mm-dd'))||'''' AS thedate
      FROM dbc.databasesv
      WHERE databasename = USER
      EXPAND ON PERIOD( DATE'2017-01-01',DATE'2017-02-01' ) AS date_range BY INTERVAL '1' DAY

This example produces the below list of date values which can be inserted into a 'create table' statement.

,DATE'2017-01-01'
,DATE'2017-01-02'
,DATE'2017-01-03'
,DATE'2017-01-04'
,DATE'2017-01-05'
,DATE'2017-01-06'
,DATE'2017-01-07'
,DATE'2017-01-08'
,DATE'2017-01-09'
,DATE'2017-01-10'
,DATE'2017-01-11'
,DATE'2017-01-12'
,DATE'2017-01-13'
,DATE'2017-01-14'
,DATE'2017-01-15'
,DATE'2017-01-16'
,DATE'2017-01-17'
,DATE'2017-01-18'
,DATE'2017-01-19'
,DATE'2017-01-20'
,DATE'2017-01-21'
,DATE'2017-01-22'
,DATE'2017-01-23'
,DATE'2017-01-24'
,DATE'2017-01-25'
,DATE'2017-01-26'
,DATE'2017-01-27'
,DATE'2017-01-28'
,DATE'2017-01-29'
,DATE'2017-01-30'
,DATE'2017-01-31'

Manually remove the first ',' character.

HTH

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: how does compress work in tables

Do you have an example of an ALC routine ?

Senior Apprentice

Re: how does compress work in tables

I don't but there are examples in the 'sql external programming' manual. Try this: https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1147_151K/UDF_Cod...

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com