All Forums Database
sai_666 7 posts Joined 03/13
20 Mar 2013
how know the difference between compress table and general table ?

how know the difference between compress table and general table ?

KS42982 137 posts Joined 12/12
21 Mar 2013

I hope when you said "compress table", you meant the table with its column values compressed as there is nothing like compress table in teradata (as per my knowledge). And you can always check if the columns of any table are compressed or not by doing SHOW TABLE and looking at its definition.

himuengg 5 posts Joined 05/12
26 Mar 2013

I agree with table <table-name> would tell you which columns are compressed...otherwise there is nothing as  such compressed table in TD...although we have compressed join index available...hope you are not talking about compressed join index?

TGooch44 8 posts Joined 09/10
27 Mar 2013

This is current as of 13.10, I'm not sure whats available in 14.0.
It depends which type of compression you're talking about.  There are 3 main types of compression Multi-Value Compression(MVC), ALC(Algorithmic Compression) AND BLC(Block-Level Compression).  
For MVC, you can query the DBC.Columns view, which has a column called CompressValueList, you can get the tables by doing:

SELECT DatabaseNAme, TableName

FROM DBC.Columns 

Where CompressValueList is not null



That will return all tables that have at least one column compressed with MVC



For ALC, you can use the same view as above, but you'll need to filter it to a CompressValueList LIKE '%COMPRESS USING%'.


For BLC, the only way that I'm aware of to get which tables have been compressed is to do a SHOWBLOCKS in ferret.

You must sign in to leave a comment.