Teradata Space

Database
Teradata Employee

Teradata Space

What are the ways of saving space in teradata even after we have done compression??

9 REPLIES
Senior Apprentice

Re: Teradata Space

Not easily.

Delete some data or use smaller data types (e.g. SMALLINT instead of INTEGER) if you can.

Dave

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

Re: Teradata Space

The scenario here is that we have performed compression on all possible columns except PI and PPI columns,still they want to save the space.so any other possibility apart from deleting data?

Teradata Employee

Re: Teradata Space

Has Block Level Compression been applied?
Teradata Employee

Re: Teradata Space

 No

Teradata Employee

Re: Teradata Space

Doing BLC is the next obvious step for saving space then.
Highlighted
Junior Supporter

Re: Teradata Space

If block level compression still doesn't free up enough space, I'd elaborate on an earlier suggestion about data types.

Execute a min(field) and a max(field) for every numeric field in your largest tables.

Compare those results to the space used by the different data types listed below.  Alter the data type for any field that falls in the range of a smaller type.  On millions of rows those kinds of changes can add up pretty fast.

 

Byteint –   1 Byte range          -128 to +127
Smallint – 2 Bytes range    -32,768 to +32,767
Integer – 4 Bytes range      +/- 2,147,483,647
Bigint – 8 Bytes range +/- 9,223,372,036,854,775,807

 

Decimal(1,0) to Decimal(2,0) – 1 Byte up to 99
Decimal(3,0) to Decimal(4,0) – 2 Bytes up to 9999
Decimal(5,0) to Decimal(9,0) – 4 Bytes up to 999,999,999
Decimal(10,0) to Decimal(18,0) – 8 Bytes up to 999,999,999,999,999,999
Decimal(19,0) to Decimal(38,0) – 16 Bytes up to
           99,999,999,999,999,999,999,999,999,999,999,999,999

 

Another test on your numeric fields would be to see how many rows fall outside the absolute value of a data types range.  If a very small number of rows fall outside that range, those might be outliers - a subject matter expect could certainly shed light on that.

Then you'd have 2 choices: 1) delete them completely from the orginal table and change the data type on the table or 2) create a 2nd table just to house those extreme rows while changing the data type on the original table.

 

One last thing - doublecheck your system parameters to see the default decimal data type that gets created from a CREATE TABLE AS statement.  At one site, I saw the default had been set to Decimal(38,0) - probably way bigger than anything you'd ever need.

Teradata Employee

Re: Teradata Space

If you have Teradata PS engaged on site they can access internal collateral to help them do this analysis:
 
DA006788
Teradata Performance - Data Type Analysis Process v1.7
 
thanks
 
dave
Teradata Employee

Re: Teradata Space

Thanks David, i hope this will help.

Teradata Employee

Re: Teradata Space

Thank you..! will try this