Compression on Indexes

Database
Enthusiast

Compression on Indexes

Hello,

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.

-Niteen

Tags (1)
6 REPLIES
Enthusiast

Re: Compression on Indexes

You should find the syntax in Teradata SQL Reference for Data Definition Statements...

I guess this is the link www.teradataforum.com/teradata_pdf/b035-1144-115a.pdf

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...

Enthusiast

Re: Compression on Indexes

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...

Enthusiast

Re: Compression on Indexes

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.

 

 

 

Enthusiast

Re: Compression on Indexes

Thank you.

Enthusiast

Re: Compression on Indexes

 Hi ,

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

Thnx,

Irfan

Enthusiast

Re: Compression on Indexes

you can't use MVC to compress Partitioning column