After removal of compression table size decreases instead of increasing.

Database
Enthusiast

After removal of compression table size decreases instead of increasing.

I had a table say T where I have column X compressed.

The size of table T is 43.61 GB.

 

Now I wanted to change the Primary index of the table and include X in the Primary index.

So I removed compression from the column X, but table size decreased to 42.9 GB instead of increasing.

 

The column X is of datatype DECIMAL(16,0) and there were 28M records for the  compressed value.

 

Can someone explain this behaviour?

6 REPLIES
Teradata Employee

Re: After removal of compression table size increases instead of decreasing.

If this table has been updated a lot since creation you could have had a lot of block splits, etc, that can increase the size.

 

to confirm this create another copy of the table without the PI change and see if it decreases also.

 

If it does, it has nothing to do with the PI change.

 

thanks

 

Dave

Senior Apprentice

Re: After removal of compression table size increases instead of decreasing.

Hi,

 

Possible explanations:

- BLC: the old table is not and the new one is

- re-blocking as part of the insert/select processing (which I assume you did during this change)

 

HTH

Dave

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

Re: After removal of compression table size increases instead of decreasing.

Created a new table with same PI. 

No change in Size.

Enthusiast

Re: After removal of compression table size increases instead of decreasing.

Hi Dave,

 

- BLC: the old table is not and the new one is

 BLC is not enabled in our database(checked with DBA) and also created a new table now but dont see any change in size.

 

- re-blocking as part of the insert/select processing (which I assume you did during this change)

can you please elabrote on this point on how re-bocking works.

 

Regards,

Chirag

Senior Apprentice

Re: After removal of compression table size increases instead of decreasing.

When you insert/select from one table to an empty one you will often find that the data bocks in the new table are built with a large average size. This is because (as @David_Roth said) an existing table may have been subject to maintenance which will cause block splits, hence smaller blocks.

 

This means tha the new able uses fewer blocks for the same amount of data.

 

Each block has some overhead (block header/trailer) and so if you eliminate enough blocks compared to the raw data volume you can find that the table get ssmaller.

 

Cheers,

Dave

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

Re: After removal of compression table size increases instead of decreasing.

Another possible reason:

Removing COMPRESS changes the number of compress bits needed, maybe this resulted in one compress byte less per row.

 

And as data blocks are word-aligned (i.e. record length id always even), the unneeded compress byte might change the record size from odd to even -> two bytes less per row.

 

Overall this might be more than the 220 MB saving due to compression.