All Forums Database
nyemul 14 posts Joined 09/12
24 Sep 2012
Compression on Indexes

Can compression be applied to indexes?
If so where can the syntax be found in documentation?
In database design document found that, compression can be applied to secondary indexes, join indexex and hash indexes. However there is no information on how it can be applied.
Thanks in advance.

Qaisar Kiani 337 posts Joined 11/05
24 Sep 2012

You should find the syntax in Teradata SQL Reference for Data Definition Statements...
I guess this is the link
In my personal view adding the compression on indexes is not a good idea as they are the frequently used columns for accessing the data, and each time the value will be decompressed and checked against the hashmap for the matches causing the overhead in queries...

Qaisar Kiani 337 posts Joined 11/05
24 Sep 2012

BTW you can't have multivalue compression on JI. But you can compress the index by grouping the columns in select statement in fixed portion and repeated portion. Something like this
SELECT (cola, colb, colc), (cold, cole, colf)...
The first three columns makes the fixed portion while the rest three are repeated. Teradata in intelligent enough to compress the fixed portion part.
But again you should be able to find more details in the maual...

pawan0608 101 posts Joined 12/07
25 Sep 2012

There are three types of compression technique available in Teradata 13.10
Multi-Value Compression (MVC) 

  • All columns except Primary Index column(s) can be defined with compression
  • Can't be defined on Secondary or other Indexes. But, you can create Secondary or other indexes on table having MVC.
  • Can be specified on a column which is part of a secondary index (although the value in the index sub-table will be uncompressed).

Algorithmic Compression (ALC)

  • Same as MVC from Index Perspective

Block Level Compression (BLC)

  • It activates outside of the CREATE TABLE statement
  • Secondary indexes are never compressed, but join indexes can be.
  • System-wide tunables are used to define the scope of compression like temporary tables, permanent journals, spool data etc.
  • Secondary Index creation process may take less time on table with BLC as compare to table without BLC, as it require less I/O, but CPU count could be high.


nyemul 14 posts Joined 09/12
25 Sep 2012

Thank you.

irfan098 10 posts Joined 07/11
18 Jun 2013

 Hi ,
Can we apply compression on a partitioning column that is not part of the PI.?

pawan0608 101 posts Joined 12/07
20 Jun 2013

you can't use MVC to compress Partitioning column

You must sign in to leave a comment.