how know the difference between compress table and general table ?

Database
Enthusiast

how know the difference between compress table and general table ?

how know the difference between compress table and general table ?

3 REPLIES
Enthusiast

Re: how know the difference between compress table and general table ?

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.

Enthusiast

Re: how know the difference between compress table and general table ?

I agree with KS....show 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?

Teradata Employee

Re: how know the difference between compress table and general table ?

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

 GROUP BY 1,2

 ;

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.