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